BookiAPI/tables.sql at master · casparemiljensen/BookiAPI · GitHub

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
-- drop tables
drop table TablePackagesBeverages;
drop table Beverages;
drop table ReservationsTablePackages;
drop table TablePackages;
drop table Reservations;
drop table Customers;
drop table Tables;
drop table VenueHours;
drop table VenueEmployees;
drop table Venues;
drop table Employees;

-- create tables

create table Employees
(
	Id int IDENTITY(1, 1),
	Name varchar(255) not null,
	Phone varchar(12) not null,
	Email varchar(255) not null,
	Password varchar(255) not null,
	EmployeeNo int not null,
	Title varchar(30) not null,
	Salt varchar(30) not null,

	primary key (Id)
);

create table Venues
(
	Id int IDENTITY(1, 1),
	Name varchar(255) not null,
	Address varchar(255) not null,
	Zip int not null,
	City varchar(255) not null,

	primary key (Id)
);


create table VenueEmployees
(
	Id int IDENTITY(1, 1),
	VenueId int not null,
	EmployeeId int not null,
	AccessLevel int not null,

	primary key (Id),
	constraint fk_venue_employees_venue foreign key (VenueId) references Venues(Id),
	constraint fk_venue_employees_employee foreign key (EmployeeId) references Employees(Id)
);

create table VenueHours
(
	Id int IDENTITY(1, 1),
	WeekDay varchar(12),
	OpenTime time not null,
	CloseTime time not null,
	VenueId int not null,

	primary key (Id),
	constraint fk_venue_hours_venue foreign key (VenueId) references Venues(Id)
);

create table Tables
(
	Id int IDENTITY(1, 1),
	NoOfSeats int not null,
	Name varchar(255),
	VenueId int not null,

	primary key (Id),
	constraint fk_tables_venue foreign key (VenueId) references Venues(Id)
);

create table Customers
(
	Id int IDENTITY(1, 1),
	Name varchar(255) not null,
	Phone varchar(12) not null,
	Email varchar(255) not null,
	Password varchar(255) not null,
	CustomerNo int not null,
	Salt varchar(30) not null,

	primary key (Id)
);

create table Reservations
(
	Id int IDENTITY(1, 1),
	ReservationNo int not null,
	DateTimeStart DateTime not null,
	DateTimeEnd DateTime not null,
	State int not null,
	CustomerId int not null,
	VenueId int not null,
	TableId int not null,
	CreatedAt DateTime not null,
	UpdatedAt DateTime not null,

	primary key (Id),
	constraint fk_reservations_customer foreign key (CustomerId) references Customers(Id),
	constraint fk_reservations_venue foreign key (VenueId) references Venues(Id),
	constraint fk_reservations_table foreign key (TableId) references Tables(Id)
);

create table TablePackages
(
	Id int IDENTITY(1, 1),
	Name varchar(255) not null,
	Price float not null,
	VenueId int not null,

	primary key (Id),
	constraint fk_table_packages_venue foreign key (VenueId) references Venues(Id)
);

create table ReservationsTablePackages
(
	Id int IDENTITY(1, 1),
	ReservationId int not null,
	TablePackageId int not null,

	primary key (Id),
	constraint fk_reservations_table_packages_reservation foreign key (ReservationId) references Reservations(Id),
	constraint fk_reservations_table_packages_table_package foreign key (TablePackageId) references TablePackages(Id),
);

create table Beverages
(
	Id int IDENTITY(1, 1),
	Name varchar(255) not null,
	Barcode varchar(255) not null,
	Description text not null,
	CostPrice float not null,
	SalesPrice float not null,
	Stock int not null,
	VenueId int not null,

	primary key (Id),
	constraint fk_beverages_venue foreign key (VenueId) references Venues(Id)
);

create table TablePackagesBeverages
(
	Id int IDENTITY(1, 1),
	TablePackageId int not null,
	BeverageId int not null,

	primary key (Id),
	constraint fk_table_packages_beverages_table_package foreign key (TablePackageId) references TablePackages(Id),
	constraint fk_table_packages_beverages_beverage foreign key (BeverageId) references Beverages(Id)
);