Subtype or Supertype: Which Database Design Best Handles Polymorphic Data?

Subtype vs. Supertype for Database Design

Background

In database design, the decision arises whether to use subtypes or not. Subtypes are used when a specific type of data has additional attributes or properties that distinguish it from other types. This approach involves creating separate tables for each subtype, leading to a potentially large number of tables.

Example: Notes on Books and Articles

Consider a database with three main tables: BOOKS, ARTICLES, and NOTES. Each book and article can have multiple notes. The initial design assigned notes to a single NOTES table with columns:

• note_id
• note_type
• note_type_id
• note_content

Alternative Design Using Separate Tables

An alternative design proposes using five tables:

• books
• articles
• notes
• book_notes
• article_notes

This design keeps book and article notes separate, simplifying data management.

Pros and Cons of Each Design

Pros of Subtype Design (Existing Design):

• Simplifies data storage by consolidating notes in one table.
• Avoids冗余 by storing notes only once.
• Requires fewer joins for data retrieval.

Cons of Subtype Design:

• May lead to data anomalies as note types can change.
• Can be complex to manage if note types proliferate.

Pros of Supertype Design (Alternative Design):

• Promotes data integrity by explicitly defining supertype and subtype relationships.
• Allows for easy addition of new publication types (e.g., magazines).
• Provides a clearer representation of data hierarchy and relationships.

Supertype/Subtype Approach

A modified approach suggests using a supertype Publication table with two subtypes: Book and Article. This model would allow for a single Note table with a foreign key to Publication, enabling joins across all publication types (Book, Article, Magazine, etc.).

The above is the detailed content of Subtype or Supertype: Which Database Design Best Handles Polymorphic Data?. For more information, please follow other related articles on the PHP Chinese website!