In RDBMS, is there a formal design principle for Concrete objects, such as Course vs CourseSession?

In designing RDBM开发者_如何转开发S 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.

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.

The relational data model, base on mathematics, prove a way to design your data model on which certain operations is correct without risk.

Unfortunatly, this kind of data model is not a suitable solution for performance issue in database. How to organize tables for certain business domain is need to consider about not only the abstract model of objects or database normalization but also performance planning on your system. Yes, the leak of abstraction.

For example, there are two design strategies for tree structure: Adjacency model and Materialized path model(The art of SQL). Which one is better is based on which operations need to be optimized.

There is a good and classical article I recommend: The Law of Leaky Abstractions

Abstraction has its price (& it is often higher than expected)
By Keith Cooper

The art of SQL, of course, the soul of database design in my opinion.