Database Design – Inheritance and Two One-to-Many Relationships

database-design

Quick schema overview:
Below is a schema being a subject of my question. In the design there are two "regular" tables questions and notes as well as two "abstract" tables:

elements, with "concrete" implementations:
- video_clips, podcasts, books, articles
markers with following "implementations":
- book_pages, article_pages and time_frames

What I would like to achieve:
I would like to be able to put a note or a question in an element. But along with the information that let me find it quicker, thus f.e. if book was my element, I would use a page marker (book_page). If podcast was my element I would use a minute marker (time_frame).

To give an example: Let's say I have:

a book B1 with 100 pages
a book B2 with 200 pages
a podcast P1 that lasts 8 minutes

Now I want to be able to put a note for page number 21 for the book B1 saying: Unlike many other metals, iron does not form amalgams with mercury. – f.e. because that statement drew my attention while reading the book and I want to highlight that by putting a note with that sentence, and connect it to B1 p. 21.

Let's say in podcast P1 in minute 4 I heard a sentence: Bank A has better mortgage plan than bank B because X Y Z. I want to be able to put a question related to P1 and minute 4 saying: Why X is better in bank A and not C?

Problems:
There are two important topics related to that design:

Inheritance
Two 1 to many relationships to the same table

I could get rid of the markers table, and relate book_pages with books, article_pages with articles and time_frames with both podcasts and video_clips. The problem is that I would have two FK in time_frames (let's assume it's acceptable with CHECK constraint) and three foreign keys in both notes and questions (book_page_id,article_page_id,time_frame_id) – and that is unacceptable. I've introduced markers table to solve that issue and have only one FK in notes and questions. Unfortunately that comes with high price, because now querying for an element needs twice more joins (for elements and markers).

I am not looking for the perfect solution because giving the problems there isn't one, I know I need to adopt the compromise but the question is if my compromise/design is acceptable or not ?

Best Answer

I think you can (and should) simplify this. Class inheritance doesn't make as much sense in SQL (DDL) as it does in OO programming languages. Instead, you should follow normalization rules.

I'd suggest a solution like this:

CREATE TABLE media (
  media_id int unsigned auto_increment PRIMARY KEY, 
  media_type enum('book', 'article', 'podcast', 'video') NOT NULL
  -- Other media columns here
  -- If you need a few columns specific to one media_type, that's OK
);

CREATE TABLE media_comment (
  media_comment_id int unsigned auto_increment PRIMARY KEY,
  media_id int unsigned NOT NULL,
  comment_type enum('question', 'note') NOT NULL,
  marker int unsigned COMMENT 'unit: seconds for video/podcast, page for book/article',      
  comment_body TEXT NOT NULL,
  CONSTRAINT media_comment_fk1 FOREIGN KEY (media_id) REFERENCES media(media_id)
);

This has the advantage of significantly simplifying your queries as well as being easier to read / understand.

If you need a few columns specific to certain media types, that's OK. These columns will then be NULL for the other media types. The overhead of NULL values is very small, though varies with database system / storage engine. See e.g. here for an explanation of the InnoDB storage engine in MariaDB and MySQL: NULL in MySQL (Performance & Storage) (StackOverflow)

Related Solutions

Constraint many to many table between two child tables

Why must the employees working on a project strictly belong to the same division that owns the project? That sounds more like a business rule that you can leave up to your application to enforce rather than something that requires DRI.

But let's say you need to enforce this using DRI. You said you have a many-to-many table associating employees with projects. Assuming you are using SQL Server, you could expand this table as follows:

CREATE TABLE dbo.Employee_Project (
     ProjectID            INT
   , ProjectDivisionID    INT

   , EmployeeID           INT
   , EmployeeDivisionID   INT

   , Hours                INT

   , CONSTRAINT FK_Project FOREIGN KEY (ProjectID, ProjectDivisionID) 
        REFRENCES dbo.Project(ProjectID, DivisionID)

   , CONSTRAINT FK_Employee FOREIGN KEY (EmployeeID, EmployeeDivisionID) 
        REFERENCES dbo.Employee(EmployeeID, DivisionID)

   , CONSTRAINT CK_EmployeeProjectSameDivision CHECK (ProjectDivisionID = EmployeeDivisionID)
);

This is what's going on here:

We have two composite foreign keys here, one for Employee and one for Project, so we can have the DivisionID for both entities in the same table and guaranteed by DRI to be correct.
These composite foreign keys require a UNIQUE index on the referenced tables. So in addition to your primary keys on EmployeeID and ProjectID in those tables, you'll need unique keys on (EmployeeID, DivisionID) and (ProjectID, DivisionID).
The CHECK constraint guarantees that an employee can only be assigned to a project owned by the same division.

If you want to see another example of this design pattern, here's the answer I gave to a design problem that had similar requirements.

Database Design Relationships – Two 1 to Many Relationships to the Same Table

Relational databases are not built to handle this situation perfectly. You have to decide what is most important to you and then make your trade-offs. You have several goals:

Maintain third normal form
Maintain referential integrity
Maintain the constraint that each account belongs to either a corporation or a natural person.
Preserve the ability to retrieve data simply and directly

The problem is that some of these goals compete with one another.

Sub-Typing Solution
You could choose a sub-typing solution where you create a super-type that incorporates both corporations and persons. This super-type would probably have a compound key of the natural key of the sub-type plus a partitioning attribute (e.g. customer_type). This is fine as far as normalization goes and it allows you to enforce referential integrity as well as the constraint that corporations and persons are mutually exclusive. The problem is that this makes data retrieval more difficult, because you always have to branch based on customer_type when you join account to the account holder. This probably means using UNION and having a lot of repetitive SQL in your query.

Two Foreign Keys Solution
You could choose a solution where you keep two foreign keys in your account table, one to corporation and one to person. This solution also allows you to maintain referential integrity, normalization and mutual exclusivity. It also has the same data retrieval drawback as the sub-typing solution. In fact, this solution is just like the sub-typing solution except that you get to the problem of branching your joining logic "sooner".

Nevertheless, a lot of data modellers would consider this solution inferior to the sub-typing solution because of the way that the mutual exclusivity constraint is enforced. In the sub-typing solution you use keys to enforce the mutual exclusivity. In the two foreign key solution you use a CHECK constraint. I know some people who have an unjustified bias against check constraints. These people would prefer the solution that keeps the constraints in the keys.

"Denormalized" Partitioning Attribute Solution
There is another option where you keep a single foreign key column on the chequing account table and use another column to tell you how to interpret the foreign key column (RoKa's OwnerTypeID column). This essentially eliminates the super-type table in the sub-typing solution by denormalizing the partitioning attribute to the child table. (Note that this is not strictly "denormalization" according to the formal definition, because the partitioning attribute is part of a primary key.) This solution seems quite simple since it avoids having an extra table to do more or less the same thing and it cuts the number of foreign key columns down to one. The problem with this solution is that it doesn't avoid the branching of retrieval logic and what's more, it doesn't allow you to maintain declarative referential integrity. SQL databases don't have the ability to manage a single foreign key column being for one of multiple parent tables.

Shared Primary Key Domain Solution
One way that people sometimes deal with this issue is to use a single pool of IDs so that there is no confusion for any given ID whether it belongs to one sub-type or another. This would probably work pretty naturally in a banking scenario, since you aren't going to issue the same bank account number to both a corporation and a natural person. This has the advantage of avoiding the need for a partitioning attribute. You could do this with or without a super-type table. Using a super-type table allows you to use declarative constraints to enforce uniqueness. Otherwise this would have to be enforced procedurally. This solution is normalized but it won't allow you to maintain declarative referential integrity unless you keep the super-type table. It still does nothing to avoid complex retrieval logic.

You can see therefore that it isn't really possible to have a clean design that follows all of the rules, while at the same time keeping your data retrieval simple. You have to decide where your trade-offs are going to be.

Best Answer

Related Solutions

Constraint many to many table between two child tables

Database Design Relationships – Two 1 to Many Relationships to the Same Table

Related Question