views:

35

answers:

1

In designing RDBMS schema, I wonder if there is formal principle of concrete objects: for example, if it is Persons table, then each record is very concrete and unique. Each record in fact represents a unique person.

But what about a table such as Courses (as in school). It can have a description, number of units, offered only in Autumn (Fall) or Spring, etc, which are the "general properties" of a course.

And then there is actual CourseSessions, which has information about the time_from and time_to (such as 10 to 11am), whether it is Monday, Wednesday or Tue / Thur, and the instructor teaching it, and also pointing back using a course_id to the Courses table.

So the above 2 tables are both needed.

Are there principles of table design for "concrete" vs "abstract"?

Update: what I mean "abstract" here is that a course is an abstract idea... there can be multiple instances of it... such as the course Physics 10 from 10-11am, and another at 12-1pm.

+1  A: 

for example, if it is Persons table, then each record is very concrete and unique. Each record in fact represents a unique person.

That is the hope, but not the reality of the situation.

By immigration or legal death status, it is possible for there to be two (or more records) that represent the same person. Uniquely identifying people is difficult - first, middle and surnames can match but actually reflect different people. SSN/SIN are not reliable, because they can change (immigration, legally dead). A name doesn't guarantee gender, and gender can be changed.

Are there principles of table design for "concrete" vs "abstract"

The classification of being "concrete" vs "abstract" is arbitrary, subject to interpretation. Does the start and end date really make a Course session "concrete"? Because I can book numerous things in [Calendaring software of choice] - doesn't mean class actually took place, or that final grades are legitimate values...

Table design is based on business rules, and the logical entities (which can become tables in the physical model) required to support those rules. Normalization helps make these entities more obvious.

OMG Ponies
so are you saying, do whatever is practical, such as Courses table, which actually has many sessions, 10-11am by Professor Lee, and 11-12pm by Profession Hanks, but then since the `course_description` and `number_of_units` is repeating too often (not 3NF), we move it to a separate table called `CourseDescriptions`? So whatever works for the practical situation?
動靜能量
OMG Ponies