Techniques for database inheritance?

by

Three common strategies:

  1. Create a table for each class in the hierarchy that contain the properties defined for each class and a foreign key back to the top-level superclass table. So you might have a vehicle table with other tables like car and airplane that have a vehicle_id column. The disadvantage here is that you may need to perform a lot of joins just to get one class type out.

  2. Create a table for each class in the hierarchy that contains all properties. This one can get tricky since it’s not easy to maintain a common ID across all the tables unless you’re using something like a sequence. A query for a superclass type would require unions against all the tables in question.

  3. Create one table for the entire class hierarchy. This eliminates joins and unions but requires that all of the columns for all class properties be in one table. You’ll probably need to leave most columns nullable since some columns won’t apply to records of a different type. For example, the vehicle table might contain a column called wingspan that corresponds to the Airplane type. If you make this column NOT NULL then any instance of a Car inserted into the table will require a value for wingspan even though a value of NULL might make more sense. If you leave the column nullable you might be able to work around this with check constraints but it could get ugly. (Single Table Inheritance)

More Related Contents:

  1. Is storing a delimited list in a database column really that bad?
  2. Should each and every table have a primary key?
  3. What’s the difference between identifying and non-identifying relationships?
  4. Database, Table and Column Naming Conventions? [closed]
  5. Can I have multiple primary keys in a single table?
  6. How are super- and subtype relationships in ER diagrams represented as tables?
  7. Normalization: What does “repeating groups” mean?
  8. Difference between scaling horizontally and vertically for databases [closed]
  9. Relational table naming convention [closed]
  10. Best practices for storing postal addresses in a database (RDBMS)?
  11. When I should use one to one relationship?
  12. What is the most efficient way to store tags in a database?
  13. Microservices with shared database? using multiple ORM’s? [closed]
  14. Why use a 1-to-1 relationship in database design?
  15. What is the optimal length for an email address in a database?
  16. What are the advantages of using a single database for EACH client?
  17. Database optimization orders
  18. In what way does denormalization improve database performance?
  19. Still Confused About Identifying vs. Non-Identifying Relationships
  20. Alternatives to Entity-Attribute-Value (EAV)?
  21. have address columns in each table or an address table that is referenced by the other tables?
  22. Minimum no of tables that exists after decomposing relation R into 1NF?
  23. Is it better to use multiple databases with one schema each, or one database with multiple schemas? [closed]
  24. Is it a good idea to use an integer column for storing US ZIP codes in a database?
  25. Limit an sqlite Table’s Maximum Number of Rows
  26. When we need to use 1-to-1 relationship in database design?
  27. Databse architecture (single db vs client specific db) for Building Enterprise Web (RIA) application on cloud
  28. Derived account balance vs stored account balance for a simple bank account?
  29. In a StackOverflow clone, what relationship should a Comments table have to Questions and Answers?
  30. uncertainty in developing a database model

Leave a Comment