Should I Use Subtypes or a Single Table with a Discriminator Column for Database Relationships?

Sub-Type Design for Database Table Relationships

When designing a database schema, one key consideration is determining whether to use subtypes or not. In the context of object-oriented programming, subtypes are derived classes that inherit properties and methods from a supertype or parent class. In a database, this concept can be applied to table relationships.

In the given scenario, the database has three major tables: BOOKS, ARTICLES, and NOTES. Each book or article can have multiple notes, and the original design proposed a single NOTES table with a discriminator column called note_type to distinguish between book notes and article notes. However, this approach may lead to data integrity challenges and redundancy.

An alternative approach suggested in the question is to use five tables:

• BOOKS
• ARTICLES
• NOTES
• BOOK_NOTES
• ARTICLE_NOTES

This design separates book and article notes into distinct tables, eliminating the need for a discriminator column. While this approach ensures data integrity, it also introduces duplication since the NOTES table is replicated for both books and articles.

Consider an alternative approach: using a supertype/subtype design with a supertype table called PUBLICATION and subtype tables for BOOKS and ARTICLES. In this scenario, the PUBLICATION table would contain columns common to both books and articles, while the subtype tables would contain columns specific to each type.

PUBLICATION {

• ID (PK)

The above is the detailed content of Should I Use Subtypes or a Single Table with a Discriminator Column for Database Relationships?. For more information, please follow other related articles on the PHP Chinese website!