AdL9r6kj

1/* 
2Etap 1: logowanie i rejestracja 
3login.php?email=&password=
4register.php?email=&name=&surname=&password=
5*/
6CREATE TABLE IF NOT EXISTS Users
7(
8Email VARCHAR(60) NOT NULL,
9Name VARCHAR(20) NOT NULL,
10Surname VARCHAR(30) NOT NULL,
11Password VARCHAR(30) NOT NULL,
12PRIMARY KEY (Email)
13);
14
15/* 
16Etap 2: tworzenie, wyszukiwanie i dołączanie do grup 
17add_group.php?name=&description=&password=&user=&color=
18edit_group.php?id=&name=&description=&password=
19edit_group_member.php?group=&user=&color=
20edit_group_member.php?group=&user=&status=
21delete_group.php?id=
22*get_users_groups.php?user=
23*add_group_tag.php
24*delete_group_tag.php
25*/
26CREATE TABLE IF NOT EXISTS Groups
27(
28Id INT NOT NULL AUTO_INCREMENT,
29Name VARCHAR(40) NOT NULL,
30Description TEXT,
31Password VARCHAR(40),
32PRIMARY KEY (Id)
33);
34
35CREATE TABLE IF NOT EXISTS GroupMembers
36(
37Group_id INT NOT NULL,
38User_id VARCHAR(60) NOT NULL,
39Marker_color VARCHAR(7) /* hex #A668C3 */,
40Status VARCHAR(10) NOT NULL, /* owner, admin, member */
41PRIMARY KEY (Group_id, User_id),
42FOREIGN KEY (Group_id) REFERENCES Groups(Id),
43FOREIGN KEY (User_id) REFERENCES Users(Id)
44);
45
46CREATE TABLE IF NOT EXISTS GroupTags
47(
48Group_id INT NOT NULL,
49Tag VARCHAR(20) NOT NULL,
50PRIMARY KEY (Group_id, Tag),
51FOREIGN KEY (Group_id) REFERENCES Groups(Id)
52);
53
54/* Etap 3: tworzenie miejsc przez grupy lub użytkowników */
55CREATE TABLE IF NOT EXISTS Spots
56(
57Id INT NOT NULL AUTO_INCREMENT,
58Name VARCHAR(30) NOT NULL,
59Latitude DECIMAL NOT NULL,
60Longitude DECIMAL NOT NULL,
61Description TEXT,
62PRIMARY KEY (Id)
63);
64
65CREATE TABLE IF NOT EXISTS SpotsOwnedByUsers
66(
67Spot_id INT NOT NULL,
68User_id INT NOT NULL,
69FOREIGN KEY (Spot_id) REFERENCES Spots(Id),
70FOREIGN KEY (User_id) REFERENCES Users(Id)
71);
72
73CREATE TABLE IF NOT EXISTS SpotsOwnedByGroups
74(
75Spot_id INT NOT NULL,
76Group_id INT NOT NULL,
77FOREIGN KEY (Spot_id) REFERENCES Spots(Id),
78FOREIGN KEY (Group_id) REFERENCES Groups(Id)
79);
80
81/* Etap 4: tworzenie wydarzeń w określonych miejscach przez grupy lub użytkowników */
82CREATE TABLE IF NOT EXISTS Events
83(
84Id INT NOT NULL AUTO_INCREMENT,
85Name VARCHAR(30) NOT NULL,
86Spot_id INT NOT NULL,
87Start_time DATETIME,
88End_time DATETIME,
89Description TEXT,
90PRIMARY KEY (Id),
91FOREIGN KEY (Spot_id) REFERENCES Spots(Id)
92);
93
94CREATE TABLE IF NOT EXISTS EventsOwnedByUsers
95(
96Event_id INT NOT NULL,
97User_id INT NOT NULL,
98FOREIGN KEY (Event_id) REFERENCES Events(Id),
99FOREIGN KEY (User_id) REFERENCES Users(Id)
100);
101
102CREATE TABLE IF NOT EXISTS EventsOwnedByGroups
103(
104Event_id INT NOT NULL,
105Group_id INT NOT NULL,
106FOREIGN KEY (Event_id) REFERENCES Events(Id),
107FOREIGN KEY (Group_id) REFERENCES Groups(Id)
108);