1

I have three tables:

  • teachers
  • classes
  • courses

The sentences is:

  • A teacher may teachs one or more courses.
  • A teacher may teachs one or more classes.
  • A teacher teachs a course for a class.

So I need a fourth table with PRIMARY KEY of each of the three tables composing the PRIMARY KEY or UNIQUE INDEX of the fourth table.

What is the correct normalization for this?

  1. The name of the table: "class_course_teacher" should be ok or I need to use a name like "assignments" for this?
  2. The primary key of the table: "class_id + course_id + teacher_id" should be ok or I need to create "id" for assignments table and this should have "class_id + course_id + teacher_id" as unique index?

2 Answers2

0

Normalization is about data structures - but not about naming. If the only requirement is "correct normalization", the both decisions are up to you.

Nevertheless good names are important in the real world. I like "assignments" - it is very meaningful.

I like to create ID-columns for each table, they make it easier to change relationships between tables afterwards.

slartidan
  • 20,403
  • 15
  • 83
  • 131
0

Normalization starts with functional dependencies. It's a method of breaking a set of information into elementary facts without losing information. Think of it as logical refactoring.

Your sentences are a start but not sufficient to determine the dependencies. Do courses have one or more teachers? Do classes have one or more teachers? Do courses have one or more classes? Do classes belong to one or more courses? Can teachers "teach" courses without classes (i.e. do you want to record which teachers can teach a course before any classes are assigned)? Do you want to define classes or courses before assigning teachers?

Your two questions don't relate to normalization. assignments is a decent name, provided you won't be recording other assignments (like homework assignments), in which case teacher_assignments or class_assignments might be better. class_course_teacher could imply that there can only be one relationship involving those three entity sets, but it can and does happen that different relationships involve the same entity sets.

I advise against using surrogate ids until you have a reason to use them. They increase the number of columns and indices required without adding useful information, and can increase the number of tables that need to be joined in a query (since you need to "dereference" the assignment_id to get to the class_id, course_id and teacher_id) unless you record redundant data (which has its own problems).

reaanb
  • 9,806
  • 2
  • 23
  • 37
  • Thank you for this explanation. I guess I'm going to `teacher_assignments`, and because this table will only be for quering which teacher teachs which course for a class I'll not create an `id` for the fourth table. – Thieres Tembra Feb 28 '16 at 04:28
  • And just btw: Do courses have one or more teachers? **YES.** Do classes have one or more teachers? **YES.** Do courses have one or more classes? **YES.** Do classes belong to one or more courses? **YES.** Can teachers "teach" courses without classes (i.e. do you want to record which teachers can teach a course before any classes are assigned)? **NO.** Do you want to define classes or courses before assigning teachers? **YES. Also teachers are defined too.** – Thieres Tembra Feb 28 '16 at 04:29
  • If any combination of class, course and teacher is a valid combination (i.e. many-to-many in every direction) then a relation consisting of all three is fully normalized. – reaanb Feb 28 '16 at 07:24
  • Surrogate Keys should not be avoided from being used. There are cases for its use. Even if they add a new column, they have advantages as well, including in terms of performance. It is only necessary to evaluate the needs of the situation. And also, you need to ask if the use of natural key (or composed primary key) is stable over time. Please look at the following addressess: https://en.wikipedia.org/wiki/Surrogate_key#Advantages http://sqlmag.com/business-intelligence/surrogate-key-vs-natural-key – Loa Mar 01 '16 at 21:32