CODEWITHSUNDEEP
sqldatabase-designdata-modeling
Shawn
Shawn

Reputation: 34279

DB design to use sub-type or not?

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_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?

Upvotes: 8

Views: 2649

Answers (5)

Damir Sudarevic
Damir Sudarevic

Reputation: 22177

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.

pub_model_01

Upvotes: 11

Michael Riley - AKA Gunny
Michael Riley - AKA Gunny

Reputation: 5184

It sounds like your main focus should be on Notes. Given that statement I would create a SuperType - SubType data structure.

Notes would contain everything that makes a note unique (SuperType) and only those items from Books & Articles that are common to all (SubType).

Note SuperType Fields:

  • NoteId
  • NoteContent
  • NoteTypeId (1=Book 2=Article)

Common Subtype Fields:

  • Title
  • Author
  • Date

Book Unique Fields: (NoteTypeId=1)

  • ISBN
  • Publisher
  • Price
  • Etc

Article Unique Fields: (NoteTypeId=2)

  • Webite
  • Distibution Rights
  • Etc

This allows peolpe to search or browse all the Notes by Content, Type, Title, Author & Date. Then for more information you drill down to the SubType details.

This also allows for growth so you can easily add other subtypes as necessary. For example Blogs (NoteTypeId=3), FaceBookPages (NoteTypeId=4), etc.

Upvotes: 0

Walter Mitty
Walter Mitty

Reputation: 18950

It depends on what you want to do with sub-typing. In your main tables books and articles seem like subtypes of "publications". However, there is no table for "publications". Is that because you don't need to search for publications, or because you didn't think in terms of "generalization-specialization relational modeling"? If you look up this phrase on the web, you'll see some good articles on the subject.

Assuming you don't need a generalized "publications" table, then you probably don't need a generalized "notes" table either. Are you going to be searching for notes where it doesn't matter which kind of publication the note refers to? How long is it going to be before you want to add a third or fourth kind of publication?

All of these have an impact on which design is "conceptually better". If you want something conceptually better, then you are optimizing, whether you realize it or not. You might be optimizing with regard to a different measure of goodness than speed or simplicity.

Upvotes: 0

OMG Ponies
OMG Ponies

Reputation: 332771

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'.

This relationship is called an arc when represented on a Logical data model.

It's fine if you don't foresee any note duplication. Not just between books, but articles too.

Upvotes: 1

Steve De Caux
Steve De Caux

Reputation: 1779

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.

Upvotes: 2

Related Questions