The following chart is the basic design of database. We will use (at least*) eight tables to store data. There are strong relationships between different tables.
User: User table is used to store users’ information.
GroupChatting: This table is used to store the chatting messages of an activity chatting group.
Notifications: This table stores the notification that users can see on their devices.
User-Activities: This table exists because the relationship between user and activity is many-to-many. This table reduces the redundant data.
Friendship: This table is used to store the friendship between two users because SoSporty allows users to add friends.
Activity: Activity table stores the basic information of activities.
PrivateChatting: This table is used to store the chatting messages between two users.
SportType: This table records the sports on SoSporty.
* The reason to use ‘at least’ here is that the author is not sure if there is any improper design in this database structure. Please feel free to point out any mistakes to help us improve it. Thanks!