Schema Design

Schema
Blue print of out database of to define how are we going to create and use our tables in a database.

It gives information about:

• Structure of Database
• Tables in Database
• Columns in a Table
• Primary Key
• Foreign Key
• Index
• Pictorial Representation

How to approach a schema design? 

Requirement:

1. Scaler will have multiple batches
2. For each batch, we need to store the name, start month and current instructor
3. Each batch of  Scaler will have multiple students.
4. Each batch has multiple classes.
5. For each class, store the name, date and time, instructor of the class.
6. For evey student, we store their name, graduation year, university name, email, phone no.
7. Every student has a buddy, who is also a student
8. A student may move from one batch to another
9. For each batch a student moves to, the date of starting is stored.
10. Every student has a mentor.
11. For every mentor, we store their name and current company name.
12. Store information about all mentor sessions ( time, duration, student, mentor, student rating, mentor rating )
13. For every batch, store if it is an Academy-batch or a DSML-batch
14. Google ( enum values ) - > just for to understand what this datatype means.

Steps to design the Schema from above requirement.

1. Create table - Identify the desired tables which need to be created.
1. Find out nouns in the requirements.
2. Need to decide if every noun need a table or not.
3. If a Noun has any line in requirement which try to tell what all entities it must have then we can make those Noun a table.
4. Naming convention for tables.
1. Plural names - Reason as table will going to have multiple records which will be related to the identity of the record attributes.
2. Snake case: If table name is of two words then mentor_sessions will be the naming convention.
3. Attribute names in singular forms - They will be singular in nature as in a record we can have only one entity not in plural format.
4. Need to add properties to attributes.
1. Primary key property:
1. unique and not null
2. constant and should not be changed.
3. Prefer number over string

Example of creating the table with details:

Tables details:

• Batches:
• name
• start_month 
• current_instructor
• batch_id (PK)
• batch_type_id(FK) refers to Batch_Types
• Students
• name
• graduation_year
• university_name
• email
• phone_no
• student_id (PK)
• batch_id (FK) refers to Batches
• buddy_id
• Classes
• name
• date
• time
• class_instructor
• class_id (PK)
• batch_id (FK) refers to Batches
• Mentors
• name
• current_company_name
• mentor_id (PK)
• Mentor_Sessions
• time
• duration
• student_rating
• mentor_rating
• student_id (FK) refers to Students
• mentor_id (FK) refers to Mentors
• session_id (PK)

Creating a lookup table:

• Student_Batch_History 
• student_id (FK) refers to Students
• batch_id (FK) refers to Batches
• start_date
• history_id(PK)
• Batch_Types
• batch_type_id
• batch_type(enum ( 'Academy_based', 'DSML_batch' )

Advantage of Batch_Type:

Save space

Disadvantage:

Less readability and need join to get information.

Cardinality of the tables.

It says that how many records of one table is linked to records of other table.

Types of Cardinalities:

1. 1:1 - It means only one record from both tables are relatable.
1. One man can marry how many woman - 1
2. One woman can marry how many man - 1
2. M:M - Here both tables have multiple records relatable with either side.
1. One Man can marry many women
2. One Woman can marry many men
3. 1:M or M:1 -
1. One record of either side is relatable to multiple records of other side table.
2. Example one person can book any seat for a movie but from movie hall side one person can seat only on one seat.

Where to add foreign key to a table as per cardinality:

1. 1:1 - We can have foreign key on either side table as we have same cardinality.
2. 1:M or M:1 - Add foreign key to only where cardinality is M.
3. M:M - In this case we need to create a lookup table which will help us to map to right records of both the tables. As we can't have a column in either of the table as one record cannot have multiple records data in a table. This lookup table is not going to have any primary key as here both foreign keys will create a composit key for identification.

Sparse Relations:

In this scenario when two tables have most of the foreign key entries as empty in that case we consider that relationship between two tables as sparse relations. And to have foreign key in one of the table is not encouraged as it is a waste of space. As most of the records are going to have null value.

As in this case we must go for lookup or mapping table. We are going to have only relevent records which form relationship as both tables can have trillions of records but out of it only 100 records are having relationship hence to save space and to make query fast and relatively cheap this lookup table is great.

Cardinality for our Scaler system tables:

1. Batches : Students -  1:M(Need to add foreign key in students table)
2. Batches : Classes - 1:M (Need to add foreign key in classes table)
3. Batch : Batch_Types - M:1 (Need to add Foreign key in Batches table)