CODEWITHSUNDEEP
sqlsql-server
bluecloud 386
bluecloud 386

Reputation: 1

Existing table design change in SQL Server

My existing table structure is below:

Students table and columns are below:

Year
Student_Id
Subject_Type_Id
Quarter
Complete_DTTM
Column1

Existing data like below:

Students:

Year       Student_Id   Quarter  Subject_Type_Id  Complete_DTTM        Column1
---------------------------------------------------------------------------------------
2006        1              1        1               Null                x
2006        1              2        1               10/2/2006           xyz
2006        1              2        2               10/30/2006          abc 
2006        1              2        3               11/20/2006          def

One student can take multiple subjects by saving each subject separately by picking from DropDownList.

Now new requirement is one student can take multiple subjects by selecting from Checkboxes and provide data for Column1 and Complete_DTTM and save.

The difference between old and new requirement is how user selecting subject types, in old from Drop down list, able to pick one subject only, Column1 and Complete_DTTM are different for each subject.

With the new requirement, they can pick multiple subjects from check boxes for a student and column1 and Complete_DTTM is same for all subjects.

NEW data going to be like below:

Year   Student_Id     Quarter   Subject_Type_Id  Complete_DTTM    Column1
--------------------------------------------------------------------------
2015       1           1         1, 2, 3, 4      12/31/2015       abcdef
2015       1           2         1, 2, 3, 4, 5   1/1/2016         xyz

How do I change ‘Students’ table (I can add new table) to support multiple subject Ids and also needs to support old data (shown above for year 2006)?

Thanks in advance

Upvotes: 0

Views: 78

Answers (2)

Wes Palmer
Wes Palmer

Reputation: 880

You need to make a table that will house just the student data. Then you will need to make a table that will house Subject Type data. The a table to link them together. 

CREATE TABLE Student (ID INT NOT NULL PRIMARY KEY, NAME VARCHAR(50) NOT NULL,n...)

Create Table for subject

CREATE TABLE Subject (ID INT NOT NULL PRIMARY KEY, Subject_Name VARCHAR(50)NOT NULL,n...)

Then You will need a table to link these two together.

CREATE TABLE Student_Suject_Link (Student_ID INT NOT NULL, SUBJECT_ID INT NOT NULL)

The link table will need to have a foreign key realtionship to the Student table and the Subject table. Student_ID needs to be a foreign key to the ID column in the student table and same for the subject table. The SUbject_ID needs to be a foreign key to the ID in the Subject table. This should satisfy 3rd normal form. And if you need to grab data it is easy enough to join tables to get what you need. I relaize it seems to create more work but it is infinately easier to manage that adding lists to tables. You can of course add other fields you deem necessary into the table it is just necessary to have the ID's to relate on. Hope this helps.

Upvotes: 1

HLGEM
HLGEM

Reputation: 96600

You now have a one to many relationship and you need a child table for the many part of the relationship. Do not ever store data in a comma delimited list. This is a way to totally break your performance when you want to find data int hose tables. It is just bad deign. You need to normalize those tables and I am sure that this is why you have been given this particular exercise.

Upvotes: 0

Related Questions