Skip to Content

Wiki source code of Data Structures

Version 8.1 by Kerem Yollu on 2025/03/21 16:24
Hide last authors
Kerem Yollu 1.1 1 = Data Structures for KPM and database relations =
2
3
4 = Models =
5
Kerem Yollu 2.2 6 * (((
7 == Address ==
8 )))
Kerem Yollu 5.2 9 ** {{code language="sql" layout="LINENUMBERS"}}DROP TABLE IF EXISTS public.address;
Kerem Yollu 1.1 10
11 CREATE TABLE public.address
12 (
13 uid SERIAL PRIMARY KEY,
Kerem Yollu 2.1 14 country CHAR(2) NOT NULL, -- ISO 3166-1 alpha-2 (e.g., 'CH')
15 region VARCHAR(100), -- State or canton
Kerem Yollu 1.1 16 city VARCHAR(100) NOT NULL,
17 postcode VARCHAR(20) NOT NULL,
18 street VARCHAR(255) NOT NULL,
Kerem Yollu 2.1 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)
Kerem Yollu 1.1 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
Kerem Yollu 2.2 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}}
Kerem Yollu 3.2 33
34
35
36 * (((
Kerem Yollu 5.1 37 == Company_Type ==
Kerem Yollu 3.2 38 )))
Kerem Yollu 5.2 39 ** {{code language="sql"}}-- Table: public.company_type
Kerem Yollu 5.1 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
Kerem Yollu 5.2 72 IS 'Timestamp for when the entry was last updated (set via application logic or triggers)';{{/code}}
73 * (((
Kerem Yollu 5.5 74 == Company_status ==
75
Kerem Yollu 6.1 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
Kerem Yollu 6.2 109 IS 'Timestamp for when the entry was last updated (set via application logic or triggers)';{{/code}}
Kerem Yollu 7.2 110
111
112
113 ----
114
115 == Company ==
Kerem Yollu 5.2 116
Kerem Yollu 8.1 117 * (((Relation))) *
118
Kerem Yollu 5.3 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
Kerem Yollu 6.2 152 IS 'Timestamp for when the entry was last updated (set via application logic or triggers)';{{/code}}
Kerem Yollu 7.1 153
Kerem Yollu 8.1 154 * Legal Form
Kerem Yollu 5.3 155
Kerem Yollu 7.1 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 )))