Sub-Type or Separate Tables for Notes: Which Database Design is Best?

Using Sub-Type or Separate Tables for Notes in Database Design

When designing a database, a common question arises: should sub-types be used within a single table, or should separate tables be created for different types of data? In this case, we have a database with three major tables: BOOKS, ARTICLES, and NOTES. Initially, a single NOTES table was used to store notes for both books and articles. However, concerns have been raised about the normalization and efficiency of this design.

A potential alternative approach is to utilize five tables: BOOKS, ARTICLES, NOTES, BOOK_NOTES, and ARTICLE_NOTES. This structure would allow for separate storage of book notes and article notes, with simpler column definitions in the NOTES table.

The decision between using sub-types or separate tables depends on the specific nature of the data. Sub-types are typically employed when the subtypes have unique and distinct columns. In this case, however, the BOOK_NOTES and ARTICLE_NOTES tables are not significantly different in structure.

A more optimal approach could involve creating a supertype table, Publication, with Book and Article as subtypes. This would allow for a single NOTE table with a foreign key to Publication. As the primary key of Publication is identical to the primary key of Book (or Article), it facilitates efficient joins on Publication, Book, or Article. This design also provides flexibility for adding new publication types, such as Magazine, without modifying the NOTES table.

For instance, the following table schema exemplifies this approach:

TABLE Publication (
      ID (PK)

The above is the detailed content of Sub-Type or Separate Tables for Notes: Which Database Design is Best?. For more information, please follow other related articles on the PHP Chinese website!