-
Notifications
You must be signed in to change notification settings - Fork 1
Database
Steven Abreu edited this page Mar 23, 2019
·
4 revisions
We will have one SQL database for the entire app.
The tables we are using are:
+----------------------+
| Tables_in_jacobsapp |
+----------------------+
| Chat |
| Event |
| EventCategory |
| EventInvite |
| EventRequest |
| GroupRequest |
| Invite |
| InviteNotification |
| Location |
| Message |
| Notification |
| OneTimeEvent |
| Organizer |
| Product |
| ProductCategory |
| RecurringAttendance |
| RecurringEvent |
| RecurringSingleEvent |
| Request |
| RequestNotification |
| SingularAttendance |
| User |
| UserGroup |
| UserGroupInvite |
| UserGroupMembership |
| UserGroupOwner |
+----------------------+
Here is an overview of these tables:
mysql> describe Chat;
+------------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+---------+------+-----+---------+----------------+
| chat_id | int(11) | NO | PRI | NULL | auto_increment |
| product_id | int(11) | NO | MUL | NULL | |
| buyer_id | int(11) | NO | MUL | NULL | |
| seller_id | int(11) | NO | MUL | NULL | |
+------------+---------+------+-----+---------+----------------+
4 rows in set (0.01 sec)
mysql> describe Event;
+--------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+----------------+
| event_id | int(11) | NO | PRI | NULL | auto_increment |
| title | varchar(50) | NO | | NULL | |
| public | bit(1) | NO | | NULL | |
| max_capacity | int(11) | YES | | NULL | |
| content | varchar(160) | YES | | NULL | |
| usergroup_id | int(11) | NO | MUL | NULL | |
| location_id | int(11) | NO | MUL | NULL | |
| evcat_id | int(11) | NO | MUL | NULL | |
+--------------+--------------+------+-----+---------+----------------+
8 rows in set (0.00 sec)
mysql> describe EventCategory;
+----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+----------------+
| evcat_id | int(11) | NO | PRI | NULL | auto_increment |
| title | varchar(30) | NO | | NULL | |
+----------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
mysql> describe EventInvite;
+-----------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+---------+------+-----+---------+-------+
| invite_id | int(11) | NO | PRI | NULL | |
| event_id | int(11) | NO | MUL | NULL | |
+-----------+---------+------+-----+---------+-------+
2 rows in set (0.01 sec)
mysql> describe EventRequest;
+------------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+---------+------+-----+---------+-------+
| request_id | int(11) | NO | PRI | NULL | |
| event_id | int(11) | NO | MUL | NULL | |
+------------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> describe GroupRequest;
+--------------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+---------+------+-----+---------+-------+
| request_id | int(11) | NO | PRI | NULL | |
| usergroup_id | int(11) | NO | MUL | NULL | |
+--------------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> describe Invite;
+-----------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+----------------+
| invite_id | int(11) | NO | PRI | NULL | auto_increment |
| user_id | int(11) | NO | MUL | NULL | |
| message | varchar(160) | NO | | NULL | |
| decision | varchar(160) | YES | | NULL | |
+-----------+--------------+------+-----+---------+----------------+
4 rows in set (0.01 sec)
mysql> describe InviteNotification;
+-----------------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+---------+------+-----+---------+-------+
| notification_id | int(11) | NO | PRI | NULL | |
| invite_id | int(11) | NO | MUL | NULL | |
+-----------------+---------+------+-----+---------+-------+
2 rows in set (0.01 sec)
mysql> describe Location;
+-------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+----------------+
| location_id | int(11) | NO | PRI | NULL | auto_increment |
| title | varchar(30) | NO | | NULL | |
+-------------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
mysql> describe Message;
+------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+----------------+
| message_id | int(11) | NO | PRI | NULL | auto_increment |
| chat_id | int(11) | NO | MUL | NULL | |
| published | datetime | NO | | NULL | |
| seen | bit(1) | NO | | NULL | |
| content | varchar(160) | NO | | NULL | |
+------------+--------------+------+-----+---------+----------------+
5 rows in set (0.01 sec)
mysql> describe Notification;
+-----------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+--------------+------+-----+---------+----------------+
| notification_id | int(11) | NO | PRI | NULL | auto_increment |
| user_id | int(11) | NO | MUL | NULL | |
| message | varchar(160) | NO | | NULL | |
+-----------------+--------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)
mysql> describe OneTimeEvent;
+------------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+---------+------+-----+---------+-------+
| event_id | int(11) | NO | PRI | NULL | |
| day | date | NO | | NULL | |
| start_time | time | NO | | NULL | |
| end_time | time | NO | | NULL | |
+------------+---------+------+-----+---------+-------+
4 rows in set (0.00 sec)
mysql> describe Organizer;
+----------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------+------+-----+---------+-------+
| user_id | int(11) | NO | PRI | NULL | |
| event_id | int(11) | NO | PRI | NULL | |
+----------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> describe Product;
+------------+---------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+---------------+------+-----+---------+----------------+
| product_id | int(11) | NO | PRI | NULL | auto_increment |
| evcat_id | int(11) | NO | MUL | NULL | |
| seller_id | int(11) | NO | MUL | NULL | |
| title | varchar(40) | NO | | NULL | |
| price | decimal(10,2) | NO | | NULL | |
| published | datetime | NO | | NULL | |
| active | bit(1) | NO | | NULL | |
| sold | bit(1) | NO | | NULL | |
| quantity | int(11) | NO | | NULL | |
+------------+---------------+------+-----+---------+----------------+
9 rows in set (0.00 sec)
mysql> describe ProductCategory;
+------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+----------------+
| prodcat_id | int(11) | NO | PRI | NULL | auto_increment |
| title | varchar(30) | NO | | NULL | |
+------------+-------------+------+-----+---------+----------------+
2 rows in set (0.01 sec)
mysql> describe RecurringAttendance;
+----------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------+------+-----+---------+-------+
| user_id | int(11) | NO | PRI | NULL | |
| event_id | int(11) | NO | PRI | NULL | |
+----------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> describe RecurringEvent;
+-------------------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------------+---------+------+-----+---------+----------------+
| recurringevent_id | int(11) | NO | PRI | NULL | auto_increment |
| event_id | int(11) | NO | MUL | NULL | |
| occurences | int(11) | NO | | NULL | |
+-------------------+---------+------+-----+---------+----------------+
3 rows in set (0.01 sec)
mysql> describe RecurringSingleEvent;
+--------------------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------------+---------+------+-----+---------+----------------+
| recurringsingle_id | int(11) | NO | PRI | NULL | auto_increment |
| recurringevent_id | int(11) | NO | MUL | NULL | |
| day | date | NO | | NULL | |
| start_time | time | NO | | NULL | |
| end_time | time | NO | | NULL | |
+--------------------+---------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
mysql> describe Request;
+------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+----------------+
| request_id | int(11) | NO | PRI | NULL | auto_increment |
| user_id | int(11) | NO | MUL | NULL | |
| message | varchar(160) | NO | | NULL | |
| decision | int(11) | YES | | NULL | |
+------------+--------------+------+-----+---------+----------------+
4 rows in set (0.01 sec)
mysql> describe RequestNotification;
+-----------------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+---------+------+-----+---------+-------+
| notification_id | int(11) | NO | PRI | NULL | |
| request_id | int(11) | NO | MUL | NULL | |
+-----------------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> describe SingularAttendance;
+----------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------+------+-----+---------+-------+
| user_id | int(11) | NO | PRI | NULL | |
| event_id | int(11) | NO | PRI | NULL | |
+----------+---------+------+-----+---------+-------+
2 rows in set (0.01 sec)
mysql> describe User;
+------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+----------------+
| user_id | int(11) | NO | PRI | NULL | auto_increment |
| first_name | varchar(40) | NO | | NULL | |
| last_name | varchar(40) | NO | | NULL | |
| email | varchar(50) | NO | | NULL | |
| activated | bit(1) | NO | | NULL | |
| joined | datetime | NO | | NULL | |
+------------+-------------+------+-----+---------+----------------+
6 rows in set (0.01 sec)
mysql> describe UserGroup;
+--------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+----------------+
| usergroup_id | int(11) | NO | PRI | NULL | auto_increment |
| title | varchar(40) | NO | | NULL | |
| max_capacity | int(11) | YES | | NULL | |
| descrip | varchar(160) | YES | | NULL | |
+--------------+--------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
mysql> describe UserGroupInvite;
+--------------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+---------+------+-----+---------+-------+
| invite_id | int(11) | NO | PRI | NULL | |
| usergroup_id | int(11) | NO | MUL | NULL | |
+--------------+---------+------+-----+---------+-------+
2 rows in set (0.01 sec)
mysql> describe UserGroupMembership;
+--------------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+---------+------+-----+---------+-------+
| user_id | int(11) | NO | PRI | NULL | |
| usergroup_id | int(11) | NO | PRI | NULL | |
+--------------+---------+------+-----+---------+-------+
2 rows in set (0.01 sec)
mysql> describe UserGroupOwner;
+--------------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+---------+------+-----+---------+-------+
| user_id | int(11) | NO | PRI | NULL | |
| usergroup_id | int(11) | NO | PRI | NULL | |
+--------------+---------+------+-----+---------+-------+
2 rows in set (0.01 sec)