CODEWITHSUNDEEP
databasedatabase-design
vvekselva
vvekselva

Reputation: 823

DataBase Modelling

I have four tables in my data model viz.,

The Student table contains the list of students with the student id as primary key.

The Elective table contains the list of elective the college offers with elective id as primary key.

The Elective Value table contains the list of the possible values for each elective. The primary key for this table is combination of the elective id and value id(Composite primary key), so for next elective the value starts from 1.

The student elective table contains the student id, elective id and value id as primary key. But I have been asked to do the following changes,

Have a id for the elective value table instead of composite primary key and use the primary key(id) in the student elective table. So the link between the elective table and the student elective breaks. May I know what is the best approach the former or the latter?

Upvotes: 0

Views: 51

Answers (1)

neoscribe
neoscribe

Reputation: 2421

The second way is generally considered the 'better' way since it is normalized and allows the elective value to change in the future with far less disruption and allows the db engine to optimize queries on the fly. And if the value is a string you may actually save some space in the db. Here's the model as I think you described it:

Student
    StudentID

Elective
    ElectiveID

ElectiveValue
    ElectiveValueID
    FK_ElectiveID
    Value

StudentElective
    StudentElectiveValueID
    FK_StudentID
    FK_ElectiveValueID

Upvotes: 1

Related Questions