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.
