Design with inheritance and two 1 to many relationships to the same table

Question

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:

1. Inheritance
2. 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 ?

Answer 1

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)