I'm currently looking for the best way to use subgroups within databases.
As you can see on the picture the "Block" object can either be a "Calendar", "Survey" or "Gallery" object. Each of the object has a different structure.
What would be the best approach to use following structure in a database like MySQL or Cassandra?
There are several options:
Single Table Inheritance: one table for all Product types, with enough columns to store all attributes of all types. This means a lot of columns, most of which are NULL on any given row.
Class Table Inheritance: one table for Products, storing attributes common to all product types. Then one table per product type, storing attributes specific to that product type.
Concrete Table Inheritance: no table for common Products attributes. Instead, one table per product type, storing both common product attributes, and product-specific attributes.
These are copied from one of my old answers: https://stackoverflow.com/a/695860/20860
As always, the "best" solution depends on how you will query the data.
