Skip to Content

Wiki source code of Data Structures

Version 5.5 by Kerem Yollu on 2025/03/21 16:13
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 * /co
77 )))
78 * (((
79 == Company_relation ==
80
81 * {{code language="sql"}}-- Table: public.company_relation
82
83 -- DROP TABLE IF EXISTS public.company_relation;
84
85 CREATE TABLE IF NOT EXISTS public.company_relation
86 (
87 uid SERIAL PRIMARY KEY,
88 name VARCHAR(100) COLLATE pg_catalog."default" NOT NULL,
89 description VARCHAR(255) COLLATE pg_catalog."default",
90 creation_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
91 updated_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
92 )
93 TABLESPACE pg_default;
94
95 ALTER TABLE IF EXISTS public.company_relation
96 OWNER TO kpm_rw;
97
98 COMMENT ON TABLE public.company_relation
99 IS 'Reference table for types of relationships a company may have (e.g., customer, partner, supplier). Used by the company table.';
100
101 COMMENT ON COLUMN public.company_relation.uid
102 IS 'Unique ID for each company relation type';
103
104 COMMENT ON COLUMN public.company_relation.name
105 IS 'Short name of the relation type (e.g., Customer, Supplier, Partner)';
106
107 COMMENT ON COLUMN public.company_relation.description
108 IS 'Optional longer description of the relation type';
109
110 COMMENT ON COLUMN public.company_relation.creation_date
111 IS 'Timestamp when the entry was created';
112
113 COMMENT ON COLUMN public.company_relation.updated_date
114 IS 'Timestamp for when the entry was last updated (set via application logic or triggers)';
115 {{/code}}
116
117
118 )))