Skip to Content

Wiki source code of Data Structures

Version 6.2 by Kerem Yollu on 2025/03/21 16:15
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 == Company_relation ==
113
114 * {{code language="sql"}}-- Table: public.company_relation
115
116 -- DROP TABLE IF EXISTS public.company_relation;
117
118 CREATE TABLE IF NOT EXISTS public.company_relation
119 (
120 uid SERIAL PRIMARY KEY,
121 name VARCHAR(100) COLLATE pg_catalog."default" NOT NULL,
122 description VARCHAR(255) COLLATE pg_catalog."default",
123 creation_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
124 updated_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
125 )
126 TABLESPACE pg_default;
127
128 ALTER TABLE IF EXISTS public.company_relation
129 OWNER TO kpm_rw;
130
131 COMMENT ON TABLE public.company_relation
132 IS 'Reference table for types of relationships a company may have (e.g., customer, partner, supplier). Used by the company table.';
133
134 COMMENT ON COLUMN public.company_relation.uid
135 IS 'Unique ID for each company relation type';
136
137 COMMENT ON COLUMN public.company_relation.name
138 IS 'Short name of the relation type (e.g., Customer, Supplier, Partner)';
139
140 COMMENT ON COLUMN public.company_relation.description
141 IS 'Optional longer description of the relation type';
142
143 COMMENT ON COLUMN public.company_relation.creation_date
144 IS 'Timestamp when the entry was created';
145
146 COMMENT ON COLUMN public.company_relation.updated_date
147 IS 'Timestamp for when the entry was last updated (set via application logic or triggers)';{{/code}}
148 )))
149
150