It is necessary in the database to store two identical in tree structure. Each tree in the final leaves has a set of attributes (for each tree its own set). Horizontal links can be established between the nodes of these two trees.

The question is - trees should be stored in different tables.

  1. Tree1
    • id
    • owner_id
    • name
    • is_leaf
  2. Tree1LeafAttributes
    • leaf_id (primary)
    • tree1_attr1
    • tree1_attr2
    • ...............
  3. Tree2
    • id
    • owner_id
    • name
    • is_leaf
  4. Tree2LeafAttributes
    • leaf_id (primary)
    • tree2_attr1
    • tree2_attr2
    • ...............
  5. Links
    • tree1_node
    • tree2_node

Or both trees stored in the same table?

  1. Trees
    • id
    • owner_id
    • name
    • tree_type
    • is_leaf
  2. Tree1LeafAttributes
    • leaf_id (primary)
    • tree1_attr1
    • tree1_attr2
    • ...............
  3. Tree2LeafAttributes
    • leaf_id (primary)
    • tree2_attr1
    • tree2_attr2
    • ...............
  4. Links
    • tree1_node
    • tree2_node

Pros of the first option: an order of magnitude easier to validate. You can not link to the wrong tree

Advantages of the second - no duplication of structure

  • All the same, it depends on the similarity of these trees in real life. By the very structure of the trees, it is not clear why we need is_leaf. And at the expense of leaf attributes in the current structure, it turns out that a single leaf can have several sets of attributes ... - Mike
  • @Mike is_leaf needed to cling attributes to them only. Roughly speaking there are files, there are folders. The folder has only a name, the file has a bunch of attributes. is_leaf is needed so that the attributes are not attached to the node. Modified the structure a bit and removed Attribute.id - Anton Shchyrov
  • The first option I like. In the second, besides the complexity of controlling external links, there is still the potential that the tree_type of the branch and its leaf will be different. - Mike
  • @Mike Thank you. Well then we will dance from him - Anton Shchyrov
  • In the second case, with one table, you can add to foreign key tree_type (or add the owner_type column and include it in foreign key (owner_id, owner_type) => (id, tree_type)), enforce check constraints and get rid of deficiencies in such a simple way. - Sergey

0