Skip to Content

Changes for page Data Structures

Last modified by Kerem Yollu on 2025/03/23 11:28
From version 7.1
edited by Kerem Yollu
on 2025/03/21 16:16
Change comment: There is no comment for this version
To version 11.1
edited by Kerem Yollu
on 2025/03/22 14:50
Change comment: There is no comment for this version

Summary

Details

Page properties
Content
... ... @@ -3,11 +3,10 @@
3 3  
4 4  = Models =
5 5  
6 -* (((
7 7  == Address ==
8 -)))
9 -** {{code language="sql" layout="LINENUMBERS"}}DROP TABLE IF EXISTS public.address;
10 10  
8 +* {{code language="sql" layout="LINENUMBERS"}}DROP TABLE IF EXISTS public.address;
9 +
11 11  CREATE TABLE public.address
12 12  (
13 13   uid SERIAL PRIMARY KEY,
... ... @@ -31,158 +31,30 @@
31 31  COMMENT ON TABLE public.address
32 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 33  
33 +== Common Stand alone tables having the same Cols. and pointing to no other table ==
34 34  
35 +{{code language="sql"}}
36 +DO $$
37 +DECLARE
38 + tname TEXT;
39 +BEGIN
40 + FOREACH tname IN ARRAY ARRAY'company_legal_form','company_relation','company_status','company_type',
41 + 'task_status', 'task_type', 'task_category',
42 + 'user_role', 'user_type',
43 + 'project_type','project_areas', 'project_person_roles', 'project_company_role']
44 + LOOP
45 + EXECUTE format('
46 + CREATE TABLE IF NOT EXISTS public.%I (
47 + uid SERIAL PRIMARY KEY,
48 + name VARCHAR(100) NOT NULL,
49 + description VARCHAR(255),
50 + creation_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
51 + updated_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
52 + );
53 + ALTER TABLE public.%I OWNER TO kpm_rw;
54 + ', tname, tname);
55 + END LOOP;
56 +END$$;
57 +{{/code}}
35 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 -)))
151 -* (((
152 -== Company_legal_form ==
153 -
154 -* {{code language="sql"}}-- Table: public.company_legal_form
155 -
156 --- DROP TABLE IF EXISTS public.company_legal_form;
157 -
158 -CREATE TABLE IF NOT EXISTS public.company_legal_form
159 -(
160 - uid SERIAL PRIMARY KEY,
161 - name VARCHAR(100) COLLATE pg_catalog."default" NOT NULL,
162 - description VARCHAR(255) COLLATE pg_catalog."default",
163 - creation_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
164 - updated_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
165 -)
166 -TABLESPACE pg_default;
167 -
168 -ALTER TABLE IF EXISTS public.company_legal_form
169 - OWNER TO kpm_rw;
170 -
171 -COMMENT ON TABLE public.company_legal_form
172 - IS 'Reference table for legal structures of companies (e.g., AG, GmbH, Ltd, Inc). Used by the company table.';
173 -
174 -COMMENT ON COLUMN public.company_legal_form.uid
175 - IS 'Unique ID for each legal form';
176 -
177 -COMMENT ON COLUMN public.company_legal_form.name
178 - IS 'Short name of the legal form (e.g., GmbH, AG, Ltd, Inc)';
179 -
180 -COMMENT ON COLUMN public.company_legal_form.description
181 - IS 'Optional longer description of the legal form';
182 -
183 -COMMENT ON COLUMN public.company_legal_form.creation_date
184 - IS 'Timestamp when the entry was created';
185 -
186 -COMMENT ON COLUMN public.company_legal_form.updated_date
187 - IS 'Timestamp for when the entry was last updated (set via application logic or triggers)';{{/code}}
188 -)))
59 +== ==