Skip to Content

Wiki source code of Data Structures

Version 6.2 by Kerem Yollu on 2025/03/21 16:15
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 5.5 110 )))
111 * (((
Kerem Yollu 5.2 112 == Company_relation ==
113
Kerem Yollu 5.3 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
Kerem Yollu 6.2 147 IS 'Timestamp for when the entry was last updated (set via application logic or triggers)';{{/code}}
148 )))
Kerem Yollu 5.3 149
Kerem Yollu 5.4 150