0

according to this post and many others web resources, you can do table inheritance with Table Per Hierarchy Inheritance, Table Per Type Inheritance or Single table (single table for all sub classes).
What about taxonomies? I do not know if "taxonomy" is the right word, I try to explain.

I have 3 entities type:

  • Organization (father)
    • Restaurant (child)
    • Hotel (child)

These are the tables I thought:

organization_type (id, name) // (ex. "restaurant", "hotel")
organization (id, name, organization_type_id)
organization_taxonomy (id, name) // (ex. "rooms_number", "tables_number")
organization_has_taxonomy (organization_id, organization_taxonomy_id, value)

If I need another organization type, I just need to:

  1. add record to organization_type table
  2. add records to organization_taxonomy table

Are there some drawbacks in this approach I am not thinking about?

1 Answer 1

1

Are there some drawbacks in this approach I am not thinking about?

Only if it becomes an EAV anti-pattern approach, which will depend on your use cases.

If your database needed to store, manage, and query specific data attributes about the different organizations such as "select all restaurants with a 5 star rating" or "update the price of any hotels with less than 100 rooms and is located in Montana", etc, then your current design would not be appropriate. As mentioned in the link I provided on the EAV anti-pattern, it would be inefficient and difficult to query, not enforce proper data integrity, and typically isn't scalable.

Your organization_taxonomy concerns me a little bit, that it may become the EAV anti-pattern, based on your comments "// (ex. "rooms_number", "tables_number")". If you plan to store multiple specific attributes of each Organization which you'll need to query off of later on, then you should store them in their own specific tables for that type of Organization. I.e. Hotels should be a dedicated table vs Restaurants being a separate table.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.