I am working in a database that has a hierarchy of Companies (From location-level all the way up to Top-level parent company). I am designing a system within that to provide a "subscription" mechanism that has implied inheritance. So for instance, Let's say you have Parent company A that has a subscription to service S. That means that all organizations from the parent company down to the location level will inherit that same subscription.
However, there is a requirement to be able to turn that subscription off. So given the above inheritance rules, you now have to be able to create an exception within the system for child companies below the parent. So you could have location company B that has a subscription to service S (inherited from company A) and location C from Parent Company A that has decided to explicitly not subscribe to service S.
My question is this: What is the best way to store this data? My first thought is this: You have 1 SQL table that stores columns for:
- The "owning" company for the subscription
- Subscription Metadata
- "Parent" subscription
- Subscription Exception (a "negative" subscription)
So a table to represent the above scenario would have 2 entries:
id| Company | Subscription | Parent Subscription | Subscription Exception
--------------------------------------------------------------------------
1 | A | {metadata X} | NULL | False
2 | C | {metadata X} | 1 | True
As an alternative, rather than having a Subscription Exception column, I could create a separate "exception" table that had the following Entry instead of the second row above:
id| Company | Subscription
---------------------------
1 | C | 1
So my question is this: What is the best way to go? Which of these options would scale better? Is there another option to scale this up when you have thousands of companies with hundreds of subscriptions each?
The way I see it - The first idea seems to be the easiest to implement with ORM. The second idea stores less redundant data. But I don't know how well either of them would scale.
