1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
| /* SQLEditor (SQLite)*/
CREATE TABLE tb_date
(
id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
i_day INTEGER NOT NULL,
i_month INTEGER NOT NULL,
i_year INTEGER NOT NULL,
i_hour INTEGER,
i_minute INTEGER
);
CREATE TABLE tb_family
(
id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
customer_primary INTEGER,
customer_secondary INTEGER,
FOREIGN KEY (customer_primary) REFERENCES tb_customer (id) ,
FOREIGN KEY (customer_secondary) REFERENCES tb_customer (id)
);
CREATE TABLE tb_language
(
id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
s_language TEXT DEFAULT 'Francais' NOT NULL UNIQUE
);
CREATE TABLE tb_location
(
id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
s_country INTEGER NOT NULL,
s_city TEXT NOT NULL,
i_postal_code INTEGER NOT NULL,
i_street_number INTEGER NOT NULL,
s_street_name TEXT NOT NULL,
b_appartment INTEGER(1,0) DEFAULT '0',
b_house INTEGER(1,0) DEFAULT '0',
i_appartment_number INTEGER
);
CREATE TABLE tb_promotion
(
id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
i_discount_pourcent INTEGER,
date_start INTEGER,
date_end INTEGER,
b_active_promotion INTEGER(1,0) DEFAULT '0',
b_recusrive_day INTEGER(1,0) DEFAULT '0' NOT NULL,
FOREIGN KEY (date_start) REFERENCES tb_date (id) ,
FOREIGN KEY (date_end) REFERENCES tb_date (id)
);
CREATE TABLE tb_customer_reservation
(
id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
customer_reservation INTEGER NOT NULL,
reservation_customer INTEGER NOT NULL,
employee_reservation INTEGER,
FOREIGN KEY (reservation_customer) REFERENCES tb_reservation (id) ,
FOREIGN KEY (customer_reservation) REFERENCES tb_customer (id) ,
FOREIGN KEY (employee_reservation) REFERENCES tb_employee (id)
);
CREATE TABLE tb_type_reservation
(
id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
s_type TEXT NOT NULL,
i_duration INTEGER DEFAULT 0 NOT NULL,
i_price INTEGER DEFAULT '0' NOT NULL,
promotion INTEGER,
FOREIGN KEY (promotion) REFERENCES tb_promotion (id)
);
CREATE TABLE tb_reservation
(
id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
date_reservation INTEGER NOT NULL,
b_canceled INTEGER(1,0) DEFAULT '0',
b_pay INTEGER(1,0) DEFAULT '0',
type_reservation INTEGER,
FOREIGN KEY (date_reservation) REFERENCES tb_date (id) ,
FOREIGN KEY (type_reservation) REFERENCES tb_type_reservation (id)
);
CREATE TABLE tb_type_unavailable
(
id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
s_type TEXT NOT NULL
);
CREATE TABLE tb_unavailable
(
id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
date_starting INTEGER NOT NULL,
date_end INTEGER,
i_duration INTEGER,
type_unavailabe INTEGER,
"b_ completed" INTEGER(0,1) DEFAULT '0',
FOREIGN KEY (type_unavailabe) REFERENCES tb_type_unavailable (id) ,
FOREIGN KEY (date_starting) REFERENCES tb_date (id) ,
FOREIGN KEY (date_end) REFERENCES tb_date (id)
);
CREATE TABLE tb_user
(
id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
s_email TEXT NOT NULL UNIQUE,
s_first_name TEXT NOT NULL,
s_last_name TEXT NOT NULL,
s_password TEXT NOT NULL,
i_phone_number INTEGER NOT NULL,
location_home INTEGER,
language_default INTEGER NOT NULL,
date_birthdate INTEGER,
b_inactive INTEGER(1,0) DEFAULT 1,
b_blocked INTEGER(1,0) DEFAULT 0,
FOREIGN KEY (location_home) REFERENCES tb_date (id) ,
FOREIGN KEY (language_default) REFERENCES tb_user (id) ,
FOREIGN KEY (date_birthdate) REFERENCES tb_language (id) ,
FOREIGN KEY (location_home) REFERENCES tb_location (id)
);
CREATE TABLE tb_work_day
(
id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
s_day TEXT NOT NULL,
i_start_minute_day INTEGER,
i_start_hour_day INTEGER NOT NULL,
i_end_minute_day INTEGER,
i_end_hour_day INTEGER NOT NULL,
i_start_hour_break INTEGER,
i_start_minute_break INTEGER,
i_end_minute_break INTEGER,
i_end_hour_break INTEGER
);
CREATE TABLE tb_customer
(
id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
user_person INTEGER,
date_inscription INTEGER NOT NULL,
customer_sponsor INTEGER,
i_fidelity_number INTEGER DEFAULT '0' NOT NULL,
b_newsletter INTEGER(1,0) DEFAULT '0',
FOREIGN KEY (date_inscription) REFERENCES tb_date (id) ,
FOREIGN KEY (customer_sponsor) REFERENCES tb_customer (id) ,
FOREIGN KEY (user_person) REFERENCES tb_user (id)
);
CREATE TABLE tb_employee
(
id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
user_person INTEGER NOT NULL,
date_start INTEGER NOT NULL,
date_end INTEGER,
b_administrator INTEGER(1,0) DEFAULT '0',
unavailabe INTEGER,
planning INTEGER,
FOREIGN KEY (user_person) REFERENCES tb_user (id) ,
FOREIGN KEY (date_start) REFERENCES tb_date (id) ,
FOREIGN KEY (date_end) REFERENCES tb_date (id) ,
FOREIGN KEY (unavailabe) REFERENCES tb_unavailable (id) ,
FOREIGN KEY (planning) REFERENCES tb_planning (id)
);
CREATE TABLE tb_planning
(
id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
work_day INTEGER,
b_recursive INTEGER(1,0) DEFAULT '0' NOT NULL,
FOREIGN KEY (work_day) REFERENCES tb_work_day (id)
); |
