CODEWITHSUNDEEP
phpsqlmysqlidatabase-designarchitecture
Kzqai
Kzqai

Reputation: 23072

Chained inheritance in sql

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:

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?

Upvotes: 1

Views: 250

Answers (1)

Troy Witthoeft
Troy Witthoeft

Reputation: 2666

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!

Upvotes: 1

Related Questions