用于将主题分配给类的Mysql表设计

Question

I was going to make a table where I can assign subjects to a class. I have various tasks related to this table like inserting marks for each subject,to know which subjects the class is studying,taking attendance for each subject etc.I am confused in deciding the table design.

First design is that i will make 3 columns and insert the values. But here i have to insert the same class many times For Eg.

ID - CLASS - SUBJECT

1 - 8th - English 2 - 8th - Maths 3 - 8th - Science

The second is the i will make separate column for each subject. But here the problem is what if i ever need to assign more subjects to a class.

ID - CLASS - SUBJECT1 - SUBJECT2 - SUBJECT3 - SUBJECT4

1 8th English Maths Computer Science 2 9th Maths Computer Science English

Please help me in deciding correct table design. Thanks

Answer 1

The occasion that you need to enter the same name more than once will cause some anomalies, such as update anomaly and deletion anomaly.

To overcome that, you need to define classes and subjects on different tables, then create a many-to-many relation.

For example:

Classes(ID*, ClassName)
Subjects(SubjectID*, SubjectName)
SubjectinClasses(ID*, SubjectID*)
    ID is foreign key to Classes
    SubjectID is foreign key to Subjects

* = (component of) PRIMARY KEY

This design will help you normalize your tables. I suggest you to seek information on "referential integrity" topic if term "foreign key" is not familiar to you.