This Social Networking Site For College students. In this document we shared Database Design of College Social Networking. The purpose of preparing this document is to explain complete database design details of our College Social Networking project. This document covers Table design and ER diagram of the project. Table schema diagram and other documents you can download in download link.
Database Design of College Social Networking is organized with 16 tables:
- course
- subject
- user
- student
- notice
- chat
- group_chat
- chat_message
- timeline
- timeline_comments
- quiz
- question
- quiz_result
- study_material
- discussion
- discussion_reply
Video Demo
Table Design of College Social Networking
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 |
ER diagram of College Social Networking
