2

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.

|improve this question
  • I really can't understand this: "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. ". 10+ columns with subject_name set to 'Math' - Why? What is your table structure? – NoChance Aug 12 '12 at 22:46
  • p.s. You may want to refer to "GradeLevel", just as a thought. I was confusing myself, while answering your question, because you can have a grade for a subject in a grade. :) That may be what Emmad is referring to also. – shannon Aug 12 '12 at 23:05
  • @EmmadKareem I added little bit more info. – Abdulaziz Aug 13 '12 at 0:02
3

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.

|improve this answer
  • +1: sounds good. AzizAG may not be familiar with this type of join or the key type you mentioned. – shannon Aug 12 '12 at 22:33
3

Mr.Brownstone's answer sounds fine, but be aware that for small amounts of data, having a perfectly "normalized" schema may be counterproductive. Let me explain a little.

You'll have three tables, and no data will necessarily be duplicated. The additional table is usually referred to as a "pivot" table, and it might not have any 'real' data in it. Instead, it has metadata. It lists all the ways in which Grade and Subjects are related.

The primary key Mr. Brownstone mentioned, composed of two columns on a pivot table, is called a "composite natural key". You can find lots of web articles saying it is evil. It's not necessarily evil, but it can be a hassle if you aren't careful. If you build a big programming structure on top of it, that abstracts you from the keys, so you can easily change the keys without changing the code much, you'll be fine. But it doesn't sound like you will be doing that. So instead, you'll end up with specialized code that handles multi-part keys. Yuck. When your web app wants to refer to a final grade for a student, it doesn't send/receive (e.g. via URLs) a single integer that represents it, instead it sends pairs of keys that represent Subject/Grade combinations.

And you can extend that thought, because you could have Subject/Grade/Semester/Year composite keys to refer to every class ever taught. Or maybe there are multiple locations or teachers for a class in a year.

So, an alternative to a "composite natural key" is a "surrogate key". Now, your Subject/Grade pairing has a representation of a single number. So you have a table with nothing but three key columns in it. You can see how that might not make your life simpler when you are developing this application. Pivot tables are generally a pain to develop for by-hand.

But, on the other hand, maybe it isn't really "Math" that you are teaching in the 7th and 8th grade. Maybe it's Geometry and Calculus. It is quite acceptable to "denormalize" your data intentionally. Many very popular open-source applications do this, and it makes them MORE powerful, not less. So now, we are back to the starting point, where you might be better off having a table that says:

id   grade   course_title
0    5       Math
1    6       Math
2    7       Geometry
3    8       Calculus
|improve this answer
  • Very valid point and an extremely well explained answer. – Mr.Brownstone Aug 12 '12 at 22:58
  • I guess I forgot trig in there somewhere ;) – shannon Aug 12 '12 at 23:01
  • I disagree with the "The additional table might not have any 'real' data in it. Instead, it has metadata. It lists all the ways in which Grade and Subjects are related." Relations are data in RDBMS. Some will say that all data are relations. Your surrogate key (in the ending design) is what has no useful information in it. The other two columns have information. With or without this surrogate key, every row has a piece of information: which subject is taught in which grade. The surrogate adds no value and is redundant. – ypercubeᵀᴹ Aug 13 '12 at 0:19
  • So I understand, do you suggest there is no Subject and no Grade table at all? Or that they are part of the design? Only that the PK is a surrogate in this join table? – ypercubeᵀᴹ Aug 13 '12 at 0:41
  • @ypercube: Why the angst, man? :) I did not deny that all metadata is data, if you stand back far enough, and I presented both sides of the argument. My only recommendation, was specifically focused on what the OP's environment sounded like, and I denounced the inherent un-contexted value of either approach. – shannon Aug 13 '12 at 0:44
3

The fairly classical way of modelling this situation is as follows:

ERD

Grade level represents the year/form/grade at which the subject is being presented. The material actually being taught is the subject. When certain material is taught to students at a given grade level, that is a course. When a particular instructor teaches a course to a given group of students in a certain place at a designated time (or set of times) that is a class.

Whether you choose to use natural or surrogate keys is a separate discussion. The fundamental structure of your data model will probably end up looking like this ERD.

|improve this answer
  • Oh sure, come in here with your pretty pictures, exactly what I just said but easier to understand. ;) +1 – shannon Aug 13 '12 at 0:55
  • @shannon - Right you are and fair enough. +1 to you for leading the way with a good answer. – Joel Brown Aug 13 '12 at 1:00
  • what did you use to draw that diagram? – user18565 Sep 19 '13 at 13:17
  • 1
    @axrwkr - I use Visio with James Martin ERD smart shapes that I built myself and a custom line pattern to give it the hand-drawn look for rough sketches. I find that if a diagram looks too formal some people won't treat it as a rough model, no matter how much you try to convince them otherwise! – Joel Brown Sep 20 '13 at 1:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.