Skip to Content

Wiki source code of Data Structures

Version 8.1 by Kerem Yollu on 2025/03/21 16:24
Show last authors
1 = Data Structures for KPM and database relations =
2
3
4 = Models =
5
6 * (((
7 == Address ==
8 )))
9 ** {{code language="sql" layout="LINENUMBERS"}}DROP TABLE IF EXISTS public.address;
10
11 CREATE TABLE public.address
12 (
13 uid SERIAL PRIMARY KEY,
14 country CHAR(2) NOT NULL, -- ISO 3166-1 alpha-2 (e.g., 'CH')
15 region VARCHAR(100), -- State or canton
16 city VARCHAR(100) NOT NULL,
17 postcode VARCHAR(20) NOT NULL,
18 street VARCHAR(255) NOT NULL,
19 number VARCHAR(20),
20 unit VARCHAR(50), -- Apartment/Suite/Floor/etc.
21 extra_address VARCHAR(255), -- Additional info
22 lon DOUBLE PRECISION, -- Longitude (optional)
23 lat DOUBLE PRECISION -- Latitude (optional)
24 )
25
26 TABLESPACE pg_default;
27
28 ALTER TABLE IF EXISTS public.address
29 OWNER to kpm_rw;
30
31 COMMENT ON TABLE public.address
32 IS 'addresses table, instead of making use of addresses for ever company, customer and co... Prefer to make addresses table and then assign it to a company or a user etc.';{{/code}}
33
34
35
36 * (((
37 == Company_Type ==
38 )))
39 ** {{code language="sql"}}-- Table: public.company_type
40
41 -- DROP TABLE IF EXISTS public.company_type;
42
43 CREATE TABLE IF NOT EXISTS public.company_type
44 (
45 uid SERIAL PRIMARY KEY,
46 name VARCHAR(100) COLLATE pg_catalog."default" NOT NULL,
47 description VARCHAR(255) COLLATE pg_catalog."default",
48 creation_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
49 updated_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
50 )
51 TABLESPACE pg_default;
52
53 ALTER TABLE IF EXISTS public.company_type
54 OWNER TO kpm_rw;
55
56 COMMENT ON TABLE public.company_type
57 IS 'Reference table for different types of companies (e.g., manufacturer, distributor, partner, etc.). Used by company table.';
58
59 COMMENT ON COLUMN public.company_type.uid
60 IS 'Unique ID for each company type';
61
62 COMMENT ON COLUMN public.company_type.name
63 IS 'Short name of the company type (e.g., Manufacturer, EMS, Partner)';
64
65 COMMENT ON COLUMN public.company_type.description
66 IS 'Optional longer description of the company type';
67
68 COMMENT ON COLUMN public.company_type.creation_date
69 IS 'Timestamp when the entry was created';
70
71 COMMENT ON COLUMN public.company_type.updated_date
72 IS 'Timestamp for when the entry was last updated (set via application logic or triggers)';{{/code}}
73 * (((
74 == Company_status ==
75
76 * {{code language="sql"}}-- Table: public.company_status
77
78 -- DROP TABLE IF EXISTS public.company_status;
79
80 CREATE TABLE IF NOT EXISTS public.company_status
81 (
82 uid SERIAL PRIMARY KEY,
83 name VARCHAR(100) COLLATE pg_catalog."default" NOT NULL,
84 description VARCHAR(255) COLLATE pg_catalog."default",
85 creation_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
86 updated_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
87 )
88 TABLESPACE pg_default;
89
90 ALTER TABLE IF EXISTS public.company_status
91 OWNER TO kpm_rw;
92
93 COMMENT ON TABLE public.company_status
94 IS 'Reference table for the status or lifecycle stage of a company (e.g., Active, Inactive, Prospect). Used by the company table.';
95
96 COMMENT ON COLUMN public.company_status.uid
97 IS 'Unique ID for each company status entry';
98
99 COMMENT ON COLUMN public.company_status.name
100 IS 'Short name of the status (e.g., Active, Archived, Lead)';
101
102 COMMENT ON COLUMN public.company_status.description
103 IS 'Optional description for the company status';
104
105 COMMENT ON COLUMN public.company_status.creation_date
106 IS 'Timestamp when the entry was created';
107
108 COMMENT ON COLUMN public.company_status.updated_date
109 IS 'Timestamp for when the entry was last updated (set via application logic or triggers)';{{/code}}
110
111
112
113 ----
114
115 == Company ==
116
117 * (((Relation))) *
118
119 * {{code language="sql"}}-- Table: public.company_relation
120
121 -- DROP TABLE IF EXISTS public.company_relation;
122
123 CREATE TABLE IF NOT EXISTS public.company_relation
124 (
125 uid SERIAL PRIMARY KEY,
126 name VARCHAR(100) COLLATE pg_catalog."default" NOT NULL,
127 description VARCHAR(255) COLLATE pg_catalog."default",
128 creation_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
129 updated_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
130 )
131 TABLESPACE pg_default;
132
133 ALTER TABLE IF EXISTS public.company_relation
134 OWNER TO kpm_rw;
135
136 COMMENT ON TABLE public.company_relation
137 IS 'Reference table for types of relationships a company may have (e.g., customer, partner, supplier). Used by the company table.';
138
139 COMMENT ON COLUMN public.company_relation.uid
140 IS 'Unique ID for each company relation type';
141
142 COMMENT ON COLUMN public.company_relation.name
143 IS 'Short name of the relation type (e.g., Customer, Supplier, Partner)';
144
145 COMMENT ON COLUMN public.company_relation.description
146 IS 'Optional longer description of the relation type';
147
148 COMMENT ON COLUMN public.company_relation.creation_date
149 IS 'Timestamp when the entry was created';
150
151 COMMENT ON COLUMN public.company_relation.updated_date
152 IS 'Timestamp for when the entry was last updated (set via application logic or triggers)';{{/code}}
153
154 * Legal Form
155
156 * {{code language="sql"}}-- Table: public.company_legal_form
157
158 -- DROP TABLE IF EXISTS public.company_legal_form;
159
160 CREATE TABLE IF NOT EXISTS public.company_legal_form
161 (
162 uid SERIAL PRIMARY KEY,
163 name VARCHAR(100) COLLATE pg_catalog."default" NOT NULL,
164 description VARCHAR(255) COLLATE pg_catalog."default",
165 creation_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
166 updated_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
167 )
168 TABLESPACE pg_default;
169
170 ALTER TABLE IF EXISTS public.company_legal_form
171 OWNER TO kpm_rw;
172
173 COMMENT ON TABLE public.company_legal_form
174 IS 'Reference table for legal structures of companies (e.g., AG, GmbH, Ltd, Inc). Used by the company table.';
175
176 COMMENT ON COLUMN public.company_legal_form.uid
177 IS 'Unique ID for each legal form';
178
179 COMMENT ON COLUMN public.company_legal_form.name
180 IS 'Short name of the legal form (e.g., GmbH, AG, Ltd, Inc)';
181
182 COMMENT ON COLUMN public.company_legal_form.description
183 IS 'Optional longer description of the legal form';
184
185 COMMENT ON COLUMN public.company_legal_form.creation_date
186 IS 'Timestamp when the entry was created';
187
188 COMMENT ON COLUMN public.company_legal_form.updated_date
189 IS 'Timestamp for when the entry was last updated (set via application logic or triggers)';{{/code}}
190 )))