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)
); |
Partager