Flexible DB structure for an entity with multiple relationships and inheritance

Question

I'm designing a database structure with some particular rules and I'm trying to define a proper data model, basically one that fits those rules.

There are 3 entities:

1. Group (fields: subgroups, items ...)
2. SubGroup (fields: group, items ...)
3. Item (fields: enabled, description ...)

Those entities have relationships:

• A SubGroup belongs to a Group (a Group owns several SubGroup)
• An Item can belong to a Group, or a SubGroup (only one at a time)

And there are special rules:

• An Item that belongs to a Group is inherited to all the related SubGroup
• An Item that is inherited by a SubGroup from its Group can be overridden (I call it inheritance because that's how it feels, something that gets inherited by a parent and can be overridden or used as it is)
  • The main override use-case I have is with the enabled field, because we may want to disable an item coming by inheritance in some cases
• Do not duplicate content (Items, in particular) in the DB (the inheritance should be done when querying the DB Model, at the application level)

This example is kept simple, but the reality is a bit more complex, there can be more "SubGroup" levels, like "SubSubGroup", so the structure should handle those gracefully as well

---

Attempt 1

At first, I imagined a traditional relationship:

And this would work fine if it wasn't for those "special rules". Because there is no way with this structure to override an inherited Item.

Therefore, I made a second attempt to handle those as well.

---

Attempt 2

I made another design attempt by using a kind of "proxy" table, basically a n-n relationship table named ItemOverride (the name is horrible but I haven't found anything better) in addition of the existing entities (Group, SubGroup and Item)

The entity ItemOverride contains the following fields:

• ItemId
• EntityType (Group or SubGroup)
• EntityId (id related to the selected EntityType)

The entity ItemOverride extends the Item entity, so that it contains the same fields, allowing any field to be overridden.

Example of content in ItemsOverride table:

+--------+------------+----------+-----------------+---------------------+
| ItemId | EntityType | EntityId | Enabled         |    OverriddenHeight |
+--------+------------+----------+-----------------+---------------------+
|     1  | group      |        1 | true            | null                |
|     2  | group      |        2 | true            | 20                  |
|     3  | subgroup   |        1 | false           |                     |
+--------+------------+----------+-----------------+---------------------+

This design seems to provide the proper structure, I've added OverriddenWidth and OverriddenHeight to illustrate overridden fields. The point being to only store what's overridden and leave other values empty so that it relies on the actual Item entity to get those values.

But, I feel like it's getting much more complicated than it needs to be and I wonder if there isn't a better way to handle this.

Here are some potential pain points I can see with this structure:

• If the entity ItemOverride inherits Item, then the fields which don't allow "null" will have to be set in the ItemOverride table, which will lead to a waste of space, duplicated content and other problems because it'll make it harder to see what's really overridden from what's inherited
• If there is no inheritance between ItemOverride and Item then the ItemOverride model will "uselessly" duplicate most of its fields, and adding/updating/removing fields will have to be done in two tables instead of one

So, I don't know if this design is "good enough", or can be improved/simplified. I'd be interested to dive-in similar project with such inherit/override needs, if you know any.

---

Summary:

My second attempt seems to fit my needs and it's what I'd use if I had to start immediately because I don't see how to improve it.

There is no data redundancy with this design, but it's still unclear how to handle the data override when a SubGroup inherits a Group's Item. If the override is limited to the required field then it's rather easy, but if it's many fields then it becomes a bit more complicated to maintain. Using model/table inheritance may complicate things also, because we don't want to store non-override values and therefore want to allow null for all fields in the ItemOverride table (which maybe isn't possible if we enforce non-nullable rules in the Item entity because they'll be applied to ItemOverride as well)

Question: Is there a way to use both inheritance and override without duplicating either the columns configuration nor the data themselves?

---

For the record, I'm using Django. Its powerful internal ORM deals gracefully with ContentTypes and DynamicRelationship, they allow to reference a dynamic entity using a model's fields. (what I've referenced as EntityType/EntityId) and that's one of the reason why the 2nd attempt design is designed that way (easier to rely on something my framework provides). But I'm open to completely different possibilities/choices.