Wiki source code of Data Structures
Version 5.5 by Kerem Yollu on 2025/03/21 16:13
Hide last authors
author | version | line-number | content |
---|---|---|---|
![]() |
1.1 | 1 | = Data Structures for KPM and database relations = |
2 | |||
3 | |||
4 | = Models = | ||
5 | |||
![]() |
2.2 | 6 | * ((( |
7 | == Address == | ||
8 | ))) | ||
![]() |
5.2 | 9 | ** {{code language="sql" layout="LINENUMBERS"}}DROP TABLE IF EXISTS public.address; |
![]() |
1.1 | 10 | |
11 | CREATE TABLE public.address | ||
12 | ( | ||
13 | uid SERIAL PRIMARY KEY, | ||
![]() |
2.1 | 14 | country CHAR(2) NOT NULL, -- ISO 3166-1 alpha-2 (e.g., 'CH') |
15 | region VARCHAR(100), -- State or canton | ||
![]() |
1.1 | 16 | city VARCHAR(100) NOT NULL, |
17 | postcode VARCHAR(20) NOT NULL, | ||
18 | street VARCHAR(255) NOT NULL, | ||
![]() |
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) | ||
![]() |
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 | ||
![]() |
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}} |
![]() |
3.2 | 33 | |
34 | |||
35 | |||
36 | * ((( | ||
![]() |
5.1 | 37 | == Company_Type == |
![]() |
3.2 | 38 | ))) |
![]() |
5.2 | 39 | ** {{code language="sql"}}-- Table: public.company_type |
![]() |
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 | ||
![]() |
5.2 | 72 | IS 'Timestamp for when the entry was last updated (set via application logic or triggers)';{{/code}} |
73 | * ((( | ||
![]() |
5.5 | 74 | == Company_status == |
75 | |||
76 | * /co | ||
77 | ))) | ||
78 | * ((( | ||
![]() |
5.2 | 79 | == Company_relation == |
80 | |||
![]() |
5.3 | 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 | |||
![]() |
5.4 | 117 | |
![]() |
5.2 | 118 | ))) |