DB design to use sub-type or not?

Question

The database I'm designing has 3 major tables: BOOKS, ARTICLES, NOTES.
Each book or article can have multiple notes, my original design was just like that, which means both notes on books and notes on articles go the 'notes' table. Here are the columns for the NOTES table:

• note_id
• note_type
• note_type_id
• note_content

NOTE_TYPE can be either 'book' or 'article'; NOTE_TYPE_ID is the FK for a book_id if the note_type is 'book' OR an article id if the note_type is 'article'.

Now I start to wonder if that's the correct(or best normalized) design. An alternative approach is to use 5 tables

books / articles / notes / book_notes / article_notes

This way I can keep book notes and article notes separately, the columns are like

'notes' { note_id, note_content } 'book_notes' { book_id, note_id } 'article_notes' { articel_id, note_id }

Which one is correct or better?

Answer 1

Maybe a bit different approach -- supertype/subtype is usually used when you have very specific columns for each subtype, like in Person supertype with Patient and Doctor subtypes. Person holds all data common to people and Patient and Doctor hold very specific columns for each one. In this example your book_notes and article_notes are not really that different.
I would rather consider having a supertype Publication with Book and Article as subtypes. Then you can have just one Note table with FK to Publication. Considering that a PK number in Publication is the same number as the [PK,FK] of Book (Article) you can do joins with notes on Publication, Book or Article. This way you can simply add another publication, like Magazine by adding a new sub-classed table and not changing anything regarding Note.

For example:

TABLE Publication (
      ID (PK)
    , Title
    , -- more columns common to any publication
)

TABLE Book (
      ID (PK) = FK to Publication
    , ISBN
    , -- more columns specific to books only
)

TABLE Article (
    ID (PK) = FK to Publication
    , -- more columns specific to articles only)

TABLE Note (
      ID (PK)
    , PublicationID = FK to Publication
    , NoteText
)

Primary key for Book and Article tables also serves as a foreign key to the Publication.

Now if we add another publication, Magazine:

TABLE Magazine (
    ID (PK) = FK to Publication
    , -- more columns specific to magazines only
)

We do not have to modify Note in any way -- and we have added columns specific to magazines only.

---