MySQL Database Design – Eliminating Duplication in Multiple Tables

best practicesdatabase-designMySQLschema

As I'm working on an educational website, I'm facing a problem with my database design scheme – I can't find the correct design that eliminates duplication in the tables I'm creating.

What I'm trying to design is [n] tables that will store subject_names, subject_id, grade_name grade_number and ofcource grade_id. But since the same subject could be taught for different grades; there will be duplication.

For example, Math will be taught for every grade which means there will (10+) columns with subject_name set to Math, but each one will have different grade_id.

The current design I have in mind(which I didn't implement yet) is creating two tables, subjects and grades and subjects table will store subject_name, subject_id and grade_id and grades table will contain grade_id, grade_name and grade_number. Unfortunately this design will contain many duplicates.

So, what's the correct way of creating such a database design?

Thanks in advance.

EDIT:
Sorry for confusion, I'm referring to grade as a year of education.

Best Answer

My recommendation would be to have the subject table with just subject_name and subject_id, have the grades table with just the grade_name and grade_id. Then create a third table called subject_grades that has the columns subject_id and grade_id, on this table create foreign keys to the subject.subject_id column and the grades.grades_id column. I'd highly recommend putting a primary key over both of the columns in this table or at least a unique key.

I hope this helps you.