A Table Design of College Social Network project would typically include several tables to store and organize the data for the different functionalities of the system. Here is an a table design for a College Social Networking project.
The database for socialnetwork is organized into 16 tables:
- course
- subject
- user
- student
- notice
- chat
- group_chat
- chat_message
- timeline
- timeline_comments
- quiz
- question
- quiz_result
- study_material
- discussion
- discussion_reply
Table Design of College Social Network
Each entity can be described as follows along with its attributes:
Table name: student
The student table stores student records.
| Column | Type | Index | Description |
| student_id | int(10) | Primary key | Student ID |
| student_name | varchar(25) | Null | Student name |
| roll_no | varchar(20) | Unique | Student Roll Number |
| Password | varchar(100) | Null | Student password |
| student_img | varchar(100) | Null | Student profile image |
| course_id | int(10) | Foreign key | Course ID |
| semester | varchar(20) | Null | Semester |
| about_student | Text | Null | About student |
| email_id | varchar(25) | Null | Email ID |
| mob_no | varchar(15) | Null | Mobile Number |
| status | varchar(10) | Null | Student status |
Table name: subject
The subject is the master table which stores subject details
| Column | Type | Index | Description |
| subject_id | int(10) | Primary key | Subject ID |
| subject | varchar(40) | Null | Subject name |
| course_id | int(10) | Foreign key | Course ID |
| note | Text | Null | Subject note |
| status | varchar(10) | Null | Subject status |
Table name: user
The user table stores administrator and staff information
| Column | Type | Index | Description |
| user_id | int(10) | Primary key | User ID |
| user_type | varchar(25) | Null | User type |
| name | varchar(25) | Null | Name |
| user_img | Text | Null | User Image |
| login_id | varchar(25) | Unique | Login ID |
| password | varchar(100) | Null | Password |
| status | varchar(10) | Null | Status |
Table name: course
The course table is the master table which stores course records
| Column | Type | Index | Description |
| course_id | int(10) | Primary key | Course ID |
| course | varchar(25) | Null | Course title |
| course_description | text | Null | Course description |
| status | varchar(10) | Null | Course status – Active or Inactive |
Table name: timeline
The timeline table stores published wallpost contents.
| Column | Type | Index | Description |
| timeline_id | int(10) | Primary key | Timeline ID |
| student_id | int(10) | Foreign key | Student ID |
| post_type | varchar(20) | Null | Post type – Photo, video or Text |
| text_message | Text | Null | Text message |
| image_path | Text | Null | Image page |
| video_path | Text | Null | Video path |
| date_time | Datetime | Null | Published date and time |
Table name: timeline_comments
The timeline comments table stores comment which is published for wallpost.
| Column | Type | Index | Description |
| timeline_comment_id | int(10) | Primary key | Timeline comment ID |
| comment_type | varchar(15) | Null | Timeline comment type |
| student_id | int(10) | Foreign key | Student ID |
| timeline_id | int(10) | Foreign key | Timeline ID |
| message | Text | Null | Comment message |
| date_time | Datetime | Null | Comment date and time |
Table name: chat
The chat table stores chat records
| Column | Type | Index | Description |
| chat_id | int(10) | Primary key | Chat ID |
| student_id1 | int(10) | Foreign key | Chat message sender ID |
| student_id2 | int(10) | Foreign key | Chat message receiver ID |
Table name: chat_message
The chat message table stores chat messages.
| Column | Type | Index | Description |
| chat_message_id | int(10) | Primary key | Chat message ID |
| chat_id | int(10) | Foreign key | Chat ID |
| group_chat_id | int(10) | Foreign key | Group chat ID |
| student_id | int(10) | Foreign key | Student ID |
| date_time | Datetime | Null | Message received date and time |
| message | Text | Null | text message |
| message_status | varchar(10) | Null | Chat message status |
Table name: group_chat
The group_chat is the table which contains group chat messages.
| Column | Type | Index | Description |
| group_chat_id | int(10) | Primary key | Group chat ID |
| course_id | int(10) | Foreign key | Course ID |
| Semester | varchar(20) | Null | Semester |
Table name: discussion
The discussion table stores discussion details.
| Column | Type | Index | Description |
| discussion_id | int(10) | Primary key | Discussion ID |
| course_id | int(10) | Foreign key | Course ID |
| semester | varchar(20) | Null | Semester |
| subject_id | int(10) | Foreign key | Subject ID |
| discussion_title | varchar(100) | Null | Discussion title |
| discussion_description | Text | Null | Discussion description |
| date_time | Datetime | Null | Date time |
| student_id | int(10) | Foreign key | Student ID |
| status | varchar(10) | Null | Discussion status |
Table name: discussion_reply
The discussion reply stores discussion reply records
| Column | Type | Index | Description |
| discussion_reply_id | int(10) | Primary key | Discussion reply ID |
| discussion_id | int(10) | Foreign key | Discussion ID |
| student_id | int(10) | Foreign key | Student ID |
| user_id | int(10) | Foreign key | User ID |
| message | Text | Null | Discussion reply Message |
| uploads | varchar(100) | Null | Uploaded documents |
| date_time | Datetime | Null | Discussion reply date and time |
Table name: notice
The notice table stores published events, news and meeting records.
| Column | Type | Index | Description |
| notice_id | int(10) | Primary key | Notice ID |
| notice_type | varchar(25) | Null | Notice type |
| user_id | int(10) | Foreign key | User ID |
| title | varchar(100) | Null | Notice title or heading |
| description | Text | Null | Notice content |
| uploads | Text | Null | Notice uploading files. |
| date_time | Datetime | Null | Notice date and time |
| status | varchar(10) | Null | Notice status |
Table name: quiz
The quiz table stores the detailed information of quiz
| Column | Type | Index | Description |
| quiz_id | int(10) | Primary key | Quiz ID |
| user_id | int(10) | Foreign key | User ID |
| course_id | int(10) | Foreign key | Course ID |
| subject_id | int(10) | Foreign key | Subject ID |
| title | varchar(100) | Null | Quiz title |
| description | Text | Null | Quiz detailed information |
Table name: question
The question table stores quiz question, options and answers
| Column | Type | Index | Description |
| quiz_question_id | int(10) | Primary key | Quiz question ID |
| quiz_id | int(10) | Foreign key | Quiz ID |
| question | Text | Null | Quiz question |
| option1 | Text | Null | Option 1 |
| option2 | Text | Null | Option 2 |
| option3 | Text | Null | Option 3 |
| option4 | Text | Null | Option 4 |
| correct_ans | varchar(10) | Null | Correct answer |
| status | varchar(10) | Null | Question status |
Table name: quiz_result
The quiz result stores published quiz result.
| Column | Type | Index | Description |
| quiz_result_id | int(10) | Primary key | Quiz result ID |
| quiz_id | int(10) | Foreign key | Quiz ID |
| student_id | int(10) | Foreign key | Student ID |
| quiz_question_id | int(10) | Foreign key | Quiz question ID |
| selected_option | varchar(10) | Foreign key | Answer ID |
| correct_ans | varchar(10) | Null | Correct answer |
| date_time | Datetime | Null | Attended Date time |
Table name: study_material
The study_material table stores uploaded study materials.
| Column | Type | Index | Description |
| study_material_id | int(10) | Primary key | Study material ID |
| course_id | int(10) | Foreign key | Course ID |
| semester | varchar(20) | Null | Semester |
| subject_id | int(10) | Foreign key | Subject ID |
| title | varchar(100) | Null | Study material title |
| user_id | int(10) | Foreign key | Uploaded faculty id |
| description | Text | Null | Study material descripton |
| uploads | Text | Null | Documents uploaded |
| date_time | Datetime | Null | Published date and time |