Skip to Content

Wiki source code of Data Structures

Version 7.2 by Kerem Yollu on 2025/03/21 16:21
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.5 116 )))
117 * (((
Kerem Yollu 7.2 118 == relation ==
Kerem Yollu 5.2 119
Kerem Yollu 5.3 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
Kerem Yollu 6.2 153 IS 'Timestamp for when the entry was last updated (set via application logic or triggers)';{{/code}}
Kerem Yollu 7.1 154
155
Kerem Yollu 6.2 156 )))
Kerem Yollu 7.1 157 * (((
Kerem Yollu 7.2 158 == Company_legal_form ==
Kerem Yollu 5.3 159
Kerem Yollu 7.1 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 )))