Database Design Approach

Example.txt

* Problem Details:
Prepare a Database for Intra-IICT Building Tournament 2019

Step:1
Think for the data fields and prepare the table on Paper [Use Pen and Paper]
table name "students"
id[auto increment], reg[unique], name[various type], dept [CSE,EEE,SWE], mobile[unique], event[Programming Contest,Software Contest]

Demo Data for the Table "students"
id | reg | name | dept | mobile | event 
1 | 2016331016 | Bappy | CSE | 01711 | Programming Contest
2 | 2016331017 | Pappy | CSE | 01711 | Software Contest
3 | 2016332016 | Ratul | SWE | 01711 | Programming Contest
4 | 2016332017 | Tetul | EEE | 01711 | Software Contest

Step:2
NOW find the fields that have only some specific values : 
dept [CSE,EEE,SWE]
event[Programming Contest,Software Contest]

Step:3
NOW Prepare Tables for the Fields

dept [CSE,EEE,SWE] >>>>
table name "depts"
id[auto increment], dept_name [various type]
Demo Data
id | dept_name
1 | CSE
2 | EEE
3 | SWE

event[Programming Contest,Software Contest] >>>>
table name "events"
id[auto increment], event_name [various type]
Demo Data
id | event_name
1 | Programming Contest
2 | Software Contest

Step 4:
NOW Reconstruct our Frist table with Foreign Key
id[auto increment], reg[unique], name[various type], dept_id [depts Table->id], mobile[unique], event_id [events Table-> id]

FINAL Tables with Data at Glance

table name "depts"
id | dept_name
1 | CSE
2 | EEE
3 | SWE

table name "events"
id | event_name
1 | Programming Contest
2 | Software Contest


Demo Data for the Table "students"
id | reg | name | dept_id | mobile | event_id 
1 | 2016331016 | Bappy | 1 | 01711 | 1
2 | 2016331017 | Pappy | 1 | 01711 | 2
3 | 2016332016 | Ratul | 3 | 01711 | 1
4 | 2016332017 | Tetul | 2 | 01711 | 2