Publication Model

This is related to a stackoverflow question on DB design and my answer there.

To summarize relationships:

  • Book, article and magazine are (sub)types of publication.
  • One publication can have many notes, a note belongs to only one publication.
  • One publication can have many authors, one author can write many publications.

Few notes on tables:

  • Publication table has columns common to all publication types.
  • Book, Article, and Magazine tables contain only specific columns for each one.
  • Primary key in the Book table also serves as a foreign key to Publication ID; same for Article and Magazine.
  • P_mm_A is a helper table for many to many relationship between Author and Publication tables.