DB design to use sub-type or not?
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.
From a certain perspective it is much better in the long run to use
books / book_notes / articles /article notes
as a design principle for your database.
When you consider backups, data manipulation and data portability over time, having the attributes of a single entity in its own table starts to pay off.
Neither is really "better" in absolute terms, it depends on context. People are used to putting anything in a cupboard that fits, academic database designers tend to create a cupboard per toothbrush.
In your context, you may decide that the extra overhead of sql insert/select/update/delete for 3 notes tables instead of only one is not worth it. In the longer term, if you go with the "1 notes table" design initially and then decide you don't like it, splitting it into 3 is not like rewriting war and peace.