1

I am trying to create a chained inheritance situation in my application architecture. I want to have various location groupings, from as large as a country down to as small as a single address, country>region>state>city>address, as well as arbitrary groupings like "blue group" and "vip locations".

You can see an example table that I just whipped up to represent possible entities example table of data that I whipped up

So Broadway Ave in NYC in New York in Northeast in USA inherit it's color of "#8b0000" all the way from the USA great-grandparent location, but would use it's own cover photo of "macys.png". British columbia would get it's color and photo from it's parent location of Canada. Etc.

Generally my goals are:

  • Every location can have it's own data or nulls.
  • Inherit traits down the line (e.g. Paris can inherit it's color from france if it doesn't have it's own)
  • Arbitrary groupings that users can create ("blue group", "underperformers")

This is just my first pass approach.

Is SQL even right for this kind of inheritance? For example, it seems unlikely that I can select data very easily from ancestors in the same table with sql, in this chained inheritance manner.

What would be better ways to go about this?

Kzqai
  • 22,588
  • 25
  • 105
  • 137
  • 1
    I would like to recommend ORMs for this purpose, see one of the good ORM "doctrine orm". I prefer ORMs for relationships and inheritance. because you have tagged PHP thats why. If SQL appication then http://stackoverflow.com/questions/3579079/how-can-you-represent-inheritance-in-a-database – BetaDev Mar 22 '17 at 15:50
  • You can do what you want in sql, but it looks to me as if you want someone to solution it for you for free... I am not surprised that nobody answered. – boggy Mar 24 '17 at 19:01
  • I also want to add that a solution may not pass ultimately criteria that is lurking behind the scenes but hasn't been specified in your question. For instance, how many rows do you want to support, millions, hundreds of millions, a few thousand and so on. You can come up with a generic/dynamic model that can support any properties but the performance might suffer and querying from it might be more difficult (not impossible). In general I personally favor using different tables for different entities. Do these entities have properties known at design time? – boggy Mar 24 '17 at 21:20
  • One correction to my first comment: "nobody answered **so far**". – boggy Mar 24 '17 at 21:23
  • Nah, just looking to mitigate the risk of going down the road of a dead-end approach. – Kzqai Mar 27 '17 at 16:50

1 Answers1

1

It looks like what we have is hierarchical data. Data that's best represented as a tree.

enter image description here

The best fit for this type of data would be XML or JSON. However, there a quite a few ways to represent this type of data in SQL too. DB Architect Bill Karwin -Author SQL Antipatterns- discusses them in detail here. Choose the best one for you.

enter image description here

Your current table design is the adjacency list. It can be used, but has some caveats. First, it can't handle an arbitrary number of levels. This means you have to know - ahead of time - the maximum levels you will support. From your example, that looks like five. USA at the top. Broadway Ave. at the bottom.

Querying a five tier adjacency list would be 5X self join, like this. 

SELECT 
    t1.name AS Lev1Name, 
    t2.name as Lev2Name, 
    t3.name as Lev3Name, 
    t4.name as Lev4Name, 
    t5.name as Lev5Name,
    COALESCE(t5.name,t4.name,t3.name,t2.name,t1.name) AS InheritedName,
    COALESCE(t5.color,t4.color,t3.color,t2.color,t1.color) AS InheritedColor
FROM MyTable AS t1
LEFT JOIN MyTable AS t2 ON t2.parent_location_id = t1.id
LEFT JOIN MyTable AS t3 ON t3.parent_location_id = t2.id
LEFT JOIN MyTable AS t4 ON t4.parent_location_id = t3.id
LEFT JOIN MyTable AS t5 ON t5.parent_location_id = t4.id
WHERE t1.name = 'USA'

This works, but ouch. It's not the most elegant. Still it may satisfy your needs. Update: Added some COALESCE columns so you can see the "inherited" values. Depending on the needs of your users, you may want to redesign that table from an adjacency list to one of the other options. Have a look at Mike Hillyer's fantastic article here on how to convert an adjacency list to a Nested Set.

Good luck with your design!

Community
  • 1
  • 1
Troy Witthoeft
  • 2,498
  • 2
  • 28
  • 37
  • 1
    The current OP's table design can handle an arbitrary number of levels easily using Recursive CTE (common-table expression). It has been in [Standard since 1999](https://en.wikipedia.org/wiki/SQL:1999#Common_table_expressions_and_recursive_queries) and it is supported by [many DBMSs](https://en.wikipedia.org/wiki/Hierarchical_and_recursive_queries_in_SQL#Common_table_expression) in 2017, just not by MySQL, as far as I know. – Vladimir Baranov Mar 27 '17 at 00:55
  • OP is using MySQL. So recursion is - unfortunately - not an option here. But good point in that arbitrary depth isn't always a limitation of the adjacency list model. – Troy Witthoeft Mar 27 '17 at 02:55
  • The Nested Sets approach is actually something I've seen before in a third-party library, just now I know -why- that approach was taken. So I may well try to use that. But before I do, I'll definitely check out the resource that you linked. This is the kind of body-check that I needed, thanks. – Kzqai Mar 27 '17 at 16:49