0

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 Rules from a upper List. A List can have multiple Items and multiple Rules 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.

Improve this question

1 Answer 1

Reset to default
1
+100

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

8 Comments

Thank you for your proposal. I wonder is the Rule many-to-many? I guess it is if it inherits to many Items/Lists, then it belongs to many different instances. If I'd like to go with the override -table, don't I need to give the Rule references to the parent tables like in my example? Do I add all my Rule fields to the override table, or is the enabled boolean enough?
@lr_optim You do not need to reference any table in the Rule table, this is handled via junction table. You can also omit enabled flag and just delete entries from a junction table to disable rules. And yeah, all fields regarding Rules should go to the Rule table. Junction tables are only for handling Relation part of ER, and enabled field is to control whether that Relation is active. It's easier to update if you have a scenario of enabling/disabling Rules frequent enough as opposed to insert/delete approach, but honestly it's a matter of taste.
@lr_optim if you delete a Rule from the Rule table, it will be deleted from all junction tables, given you describe FKs with on delete cascade, if you need a more granular control — just delete an entry from a junction table. Also, for inheritance, you will need to implement that manually: when creating a List B inside List A, you will need to copy A's references in a junction table with B's list_id. This also is needed to be done if you plan on having inheritance work dynamically (ie you add new Rule to A and want it to also appear in child B — needs implementation)
@lr_optim you will have a reference between Rules and Items, it's just not gonna be direct but rather via junction table. If you want to query all Rules for a given Item, this will do: select * from rule where id in (select rule_id from rule_to_item where item_id = ?)
@lr_optim that's what you have to handle on the application side. You just delete from rule_to_item where item_id = $1 and then insert into rule_to_item (item_id, rule_id) select $1, rule_id from rule_to_list where list_id = $2, where $1 is your item ID, and $2 is the list ID you move it to.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.