Skip to Content

Wiki source code of Data Structures

Version 7.2 by Kerem Yollu on 2025/03/21 16:21
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 * (((
118 == relation ==
119
120 * {{code language="sql"}}-- Table: public.company_relation
121
122 -- DROP TABLE IF EXISTS public.company_relation;
123
124 CREATE TABLE IF NOT EXISTS public.company_relation
125 (
126 uid SERIAL PRIMARY KEY,
127 name VARCHAR(100) COLLATE pg_catalog."default" NOT NULL,
128 description VARCHAR(255) COLLATE pg_catalog."default",
129 creation_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
130 updated_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
131 )
132 TABLESPACE pg_default;
133
134 ALTER TABLE IF EXISTS public.company_relation
135 OWNER TO kpm_rw;
136
137 COMMENT ON TABLE public.company_relation
138 IS 'Reference table for types of relationships a company may have (e.g., customer, partner, supplier). Used by the company table.';
139
140 COMMENT ON COLUMN public.company_relation.uid
141 IS 'Unique ID for each company relation type';
142
143 COMMENT ON COLUMN public.company_relation.name
144 IS 'Short name of the relation type (e.g., Customer, Supplier, Partner)';
145
146 COMMENT ON COLUMN public.company_relation.description
147 IS 'Optional longer description of the relation type';
148
149 COMMENT ON COLUMN public.company_relation.creation_date
150 IS 'Timestamp when the entry was created';
151
152 COMMENT ON COLUMN public.company_relation.updated_date
153 IS 'Timestamp for when the entry was last updated (set via application logic or triggers)';{{/code}}
154
155
156 )))
157 * (((
158 == Company_legal_form ==
159
160 * {{code language="sql"}}-- Table: public.company_legal_form
161
162 -- DROP TABLE IF EXISTS public.company_legal_form;
163
164 CREATE TABLE IF NOT EXISTS public.company_legal_form
165 (
166 uid SERIAL PRIMARY KEY,
167 name VARCHAR(100) COLLATE pg_catalog."default" NOT NULL,
168 description VARCHAR(255) COLLATE pg_catalog."default",
169 creation_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
170 updated_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
171 )
172 TABLESPACE pg_default;
173
174 ALTER TABLE IF EXISTS public.company_legal_form
175 OWNER TO kpm_rw;
176
177 COMMENT ON TABLE public.company_legal_form
178 IS 'Reference table for legal structures of companies (e.g., AG, GmbH, Ltd, Inc). Used by the company table.';
179
180 COMMENT ON COLUMN public.company_legal_form.uid
181 IS 'Unique ID for each legal form';
182
183 COMMENT ON COLUMN public.company_legal_form.name
184 IS 'Short name of the legal form (e.g., GmbH, AG, Ltd, Inc)';
185
186 COMMENT ON COLUMN public.company_legal_form.description
187 IS 'Optional longer description of the legal form';
188
189 COMMENT ON COLUMN public.company_legal_form.creation_date
190 IS 'Timestamp when the entry was created';
191
192 COMMENT ON COLUMN public.company_legal_form.updated_date
193 IS 'Timestamp for when the entry was last updated (set via application logic or triggers)';{{/code}}
194 )))