Designing SQL database to represent OO class hierarchy

by

In general I prefer obtion “B” (i.e. one table for base class and one table for each “concrete” subclass).

Of course this has a couple of drawbacks: first of all you have to join at least 2 tables whenever you have to read a full instance of a subclass. Also, the “base” table will be constantly accessed by anyone who has to operate on any kind of note.

But this is usually acceptable unless you have extreme cases (billions of rows, very quick response times required and so on).

There is a third possible option: map each subclass to a distinct table. This helps partitioning your objects but costs more in development effort, in general.

See this for a complete discussion.

(Regarding your “C” solution, using VARIANT: I can’t comment on the merits/demerits, because it looks like a proprietary solution – what is it ? Transact-SQL? and I am not familiar with it).

More Related Contents:

  1. How to implement a many-to-many relationship in PostgreSQL?
  2. How to implement one-to-one, one-to-many and many-to-many relationships while designing tables?
  3. Create unique constraint with null columns
  4. Subqueries vs joins
  5. What is the purpose of system table master..spt_values and what are the meanings of its values?
  6. Database design for a survey [closed]
  7. Database Structure for Tree Data Structure [closed]
  8. Are soft deletes a good idea? [duplicate]
  9. Database Design for Revisions?
  10. Database Design for Tagging [closed]
  11. Storing money in a decimal column – what precision and scale? [closed]
  12. How to store a list in a column of a database table
  13. Serial numbers per group of rows for compound key
  14. PostgreSQL: Which Datatype should be used for Currency?
  15. Facebook database design?
  16. How to create a multi-tenant database with shared table structures?
  17. Max length for client ip address [duplicate]
  18. Name database design notation you prefer and why?
  19. Can I set ignore_dup_key on for a primary key?
  20. Postgres constraint for unique datetime range
  21. Select (retrieve) all records from multiple schemas using Postgres
  22. Which datatype should be used for currency?
  23. Use email address as primary key?
  24. Composite Primary Keys : Good or Bad?
  25. SQL ON DELETE CASCADE, Which Way Does the Deletion Occur?
  26. Which database design gives better performance?
  27. What does ON [PRIMARY] mean?
  28. Primary key/foreign Key naming convention [closed]
  29. How important are lookup tables?
  30. can we have a foreign key which is not a primary key in any other table?

Leave a Comment