CODEWITHSUNDEEP
databasepostgresqlinheritancedatabase-designrelational-database
lr_optim
lr_optim

Reputation: 381

Handling inherited and not inherited values in PostgreSQL

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:

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.

Upvotes: 0

Views: 647

Answers (1)

Evgeniy Chekan
Evgeniy Chekan

Reputation: 2665

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.

Upvotes: 1

Related Questions