Database: SynergyDatabase.sql

create table user(
id int(6),
username varchar(10) UNIQUE NOT NULL,
passhash varchar(30),
fullname varchar(30),
admin_bit int(1),
PRIMARY KEY(id)
);

--NEW TABLES ADDED
create table location(
location_id int(3),
location_name varchar(100),
description varchar(200),
PRIMARY KEY(location_id)
);

create table room(
room_id int(3),
room_name varchar(50),
description varchar(200),
PRIMARY KEY(room_id)
);

create table equipment(
part_id int(6),
name varchar(20),
description varchar(100),
quantity int(3),
PRIMARY KEY(part_id)
);
--END New Tables

create table rooms_booked(
room_id int(3),
begin DATETIME,
end DATETIME,
FOREIGN KEY(room_id) REFERENCES room(room_id)
);

create table meeting_final(
meeting_id int(10),
name varchar(20) NOT NULL,
begin DATETIME,
end DATETIME,
location_id int(3),
room_id int(3),
description varchar(100),
PRIMARY KEY(meeting_id,begin),
FOREIGN KEY(location_id) REFERENCES location(location_id),
FOREIGN KEY(room_id) REFERENCES room(room_id)
);

create table participants_final(
participant_id int(10),
user_id int(6),
meeting_id int(10),
active_bit int(1),
important_bit int(1),
PRIMARY KEY(participant_id,user_id,meeting_id),
FOREIGN KEY(user_id) REFERENCES user(id),
FOREIGN KEY(meeting_id) REFERENCES meeting_final(meeting_id)
);

create table equipment_needed_final(
participant_id int(10),
user_id int(6),
meeting_id int(10),
equipment_id int(5),
count int(100),
PRIMARY KEY(participant_id,user_id,meeting_id),
FOREIGN KEY(user_id) REFERENCES user(id),
FOREIGN KEY(meeting_id) REFERENCES meeting_final(meeting_id),
FOREIGN KEY(participant_id) REFERENCES participants_final(participant_id),
FOREIGN KEY(equipment_id) REFERENCES equipment(part_id)
);

create table meeting_pending(
meeting_id int(10),
name varchar(20) NOT NULL,
begin DATETIME,
end DATETIME,
description varchar(100),
location_id int(3),
room_id int(3),
PRIMARY KEY(meeting_id,begin),
FOREIGN KEY(location_id) REFERENCES location(location_id),
FOREIGN KEY(room_id) REFERENCES room(room_id)
);

create table participants_pending(
participant_id int(10),
user_id int(6),
meeting_id int(10),
active_bit int(1),
important_bit int(1),
PRIMARY KEY(participant_id,user_id,meeting_id),
FOREIGN KEY(user_id) REFERENCES user(id),
FOREIGN KEY(meeting_id) REFERENCES meeting_final(meeting_id)
);

create table equipment_needed_pending(
participant_id int(10),
user_id int(6),
meeting_id int(10),
equipment_id int(5),
count int(100),
PRIMARY KEY(participant_id,user_id,meeting_id),
FOREIGN KEY(user_id) REFERENCES user(id),
FOREIGN KEY(meeting_id) REFERENCES meeting_final(meeting_id),
FOREIGN KEY(participant_id) REFERENCES participants_final(participant_id),
FOREIGN KEY(equipment_id) REFERENCES equipment(part_id)
);

create table calendar_inclusion(
id int(6),
name varchar(10) NOT NULL,
url varchar(50) NOT NULL,
last_update DATETIME,
PRIMARY KEY(id)
);

create table calendar_exclusion(
id int(6),
name varchar(10) NOT NULL,
url varchar(50) NOT NULL,
last_update DATETIME,
PRIMARY KEY(id)
);

create table calendar_preference(
id int(6),
name varchar(10) NOT NULL,
url varchar(50) NOT NULL,
last_update DATETIME,
PRIMARY KEY(id)
);

create table appointments_inclusion(
calender_inclusion_id int(6),
begin DATETIME,
end DATETIME,
name varchar(10),
description varchar(50),
PRIMARY KEY(calender_inclusion_id),
FOREIGN KEY(calender_inclusion_id) REFERENCES calendar_inclusion(id)
);

create table appointments_exclusion(
calender_exclusion_id int(6),
begin DATETIME,
end DATETIME,
name varchar(10),
description varchar(50),
PRIMARY KEY(calender_exclusion_id),
FOREIGN KEY(calender_exclusion_id) REFERENCES calendar_exclusion(id)
);

create table appointments_preference(
calender_preference_id int(6),
begin DATETIME,
end DATETIME,
name varchar(10),
description varchar(50),
PRIMARY KEY(calender_preference_id),
FOREIGN KEY(calender_preference_id) REFERENCES calendar_preference(id)
);