I'm trying to draw database schema and struggling with inheritance. Let me tell with an example:
I have tables List
and Item
:
Table List{
id varchar [not null, pk]
parent_list_id varchar [ref: - List.id]
}
Table Item{
id varchar [not null, pk]
list_id varchar [ref: > List.id]
}
Table Rule{
id varchar [not null, pk]
list_id varchar [ref: > List.id]
item_id varchar [ref: > Item.id]
}
There can be multiple lists and they inherit their Rule
s from a upper List
. A List
can have multiple Item
s and multiple Rule
s and all Lists and Items that belong to a List should inherit their Rules from the parent List. Sub Lists and Items can also set their own Rules and in case of a List the Rules are inherited to their sub Lists.
An example:
- List_A
- Rule_A (set exclusively to List_A)
- Rule_B (set exclusively to List_A)
- List_B
- Rule_A (inherited)
- Rule_B (inherited)
- Rule_C (set exclusively to List_B)
- Item_A
- Rule_A (inherited)
- Rule_B (inherited)
- Rule_C (inherited)
- Rule_D (set exclusively to Item_A)
Now, this shouldn't be such a big problem, but I also need to be able to set rules active or inactive. I considered setting a boolean active
to my Model Rule
but I think there are problems with that approach. If I want to set Rule_A
and Rule_B
to be inactive for Item_A
(they would still be active under List_A
and List_B
), that is a problem since my Rules would have a list_id
referring to the List_A
. I would be really grateful if someone had some suggestions how to handle this kind of inheritance, I'm a bit stuck here.
CodePudding user response:
What you have here is, paraphrasing your description in Entity–Relation terms:
- A single Item (or a List) can have multiple Rules relating to it.
- A single Rule can have multiple Items (or Lists) relating to it.
- (The inheritance aspect is out of this model, imo, and is basically a "prefill Rule–Item relation when there's a copy created", if that makes sense).
Which is a classic many-to-many case. And the most obvious way of handling that kind of a relation with a junction tables. What's cool about those, is that you can have some additional fields in the junction table that hold some additional attributes. For example enabled boolean
:
Table List {
id varchar [not null, pk]
parent_list_id varchar [ref: - List.id]
}
Table Item {
id varchar [not null, pk]
list_id varchar [ref: > List.id]
}
Table Rule {
id varchar [not null, pk]
}
Table rule_to_list {
-- composite PK (list_id, rule_id)
list_id varchar [ref: > List.id]
rule_id varchar [ref: > Rule.id]
enabled boolean
}
Table rule_to_item {
-- composite PK (item_id, rule_id)
item_id varchar [ref: > Item.id]
rule_id varchar [ref: > Rule.id]
enabled boolean
}
With this schema you will be able to easily enable/disable individual rules in regards to specific list/item, and even remove them completely if there's a need for that.
The only thing you will need to implement, is copying parent item/list rules to junction tables, but that's pretty easy. Or you can go with implicit "all items/lists inherit rules from their parents unless there's an override" (ie parent list has rule A enabled, and child list has A explicitly disabled in a junction table) and just traverse up the hierarchy when computing rule list for an item/list.