Databases
StackOverflow2013
Postsdbo
PORepresenting a many-many and a one-many as a single denormalized view, as a many-to-many, or another way?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PORepresenting a many-many and a one-many as a single denormalized view, as a many-to-many, or another way?
    primarykey
    data
    text
    <p>I have a model question, followed by a view question in a separate SO post, which I will link to when I create it. I'm making two posts so I can do two acceptances. </p> <p>Now, let's start by supposing that I have the following schema. This is a simplified version of a real schema I'm designing, with irrelevant columns ommitted. Names have been changed to protect the innocent, and my job.</p> <p><code>tree</code> has the following attributes.</p> <pre><code>tree.id tree.dob tree.height tree.forestid </code></pre> <p><code>forestid</code> is a foreign key to the <code>forest</code> table, which is just a way to aggregate multiple <code>tree</code> rows. It has an <code>id</code> column and some metadata columns.</p> <p><code>tree</code> and <code>forest</code> both have potentially many <code>treedata</code> rows. <code>treedata</code> contains</p> <pre><code>treedata.value treedata.treeid treedata.forestid </code></pre> <p>treedata.treeid and treedata.forestid are constrained so that one of the two must be null.</p> <p>If tree.forestid is not null, then the relationship between treedata and tree is many-to-many, and forest is the linker table. Otherwise, the relationship between tree and treedata is one to many. It is very important to my application that the user is able to group trees together into forests on the fly via the UI, and to set a treedata.value for the entire forest, but also be able to work with individual trees. Now, I can think of a couple of ways to represent this. One is to say that every tree has a forestid and is a forest of at least size 1. Then, the relationship is always many to many. Another is to provide a denormalized view along the lines of </p> <pre><code>select tree.*, treedata.* from tree, treedata where tree.id = treedata.treeid union select tree.*, treedata.*, from tree, treedata where tree.forestid = treedata.forestid. </code></pre> <p>Yet a third way would be to have a <code>forestid</code> column in <code>tree</code> and to drop the <code>forest</code> table entirely. Down that path I see a difficulty in getting ACID guarantees with respect to proper incrementing of <code>tree.forestid</code>. There's also the possibility that forests should contain their own metadata. I'd love to hear more ways to represent this, and also to get the opinion of more seasoned database people with respect to which way is preferable, and top marks if you explain why you think so by citing examples from your own experience.</p> <p><em>Response to Martin Dom's suggestion of a TreeComposite table:</em></p> <p>Thanks for your reply. I wanted to give the suggestion of a TreeComposite a day to simmer before responding. First of all, your way does model the relationship I'm expressing in normal form, so yes, I think that you do understand the question. However, I think I made a silly mistake by naming my tables Tree and Forest: because Forests don't need to be recursively composable into each other. They aren't computer science trees. They're just bark-and-twig trees. The parentid model, though it still represents what I need in normal form (it's a generalization of what I need), and though it has the advantage that Trees and Forests are "the same thing" now, which is on the surface a complexity win, I fear that in the soil it would be a tangled mess.</p> <p>The problem is that whether or not they're called the same thing in my model, they're not not the same thing to my controller or view. E.G., a TreeComposite with child nodes will probably have at least attribute for which each node will have a distinct value. In that case, I need to use a different widget in my view to display multiple values for the attribute. Put another way,I need to be able to display each TreeComposite that is its own parent as a single row, and what that row looks like depends on whether the TreeComposite has children.</p> <p>So the first thing I have to do after I extract a TreeComposite from my model is decide whether it's "really" a Tree or a Forest. Why do that, when I can store it in normal form as Tree and Forest directly, thereby making my View and Controllers simpler without hurting my model? Searching for TreeData is also complicated by the parent-child relationship. I have to connect through N number of TreeComposites until I find the root node, and then search for TreeData pointing at the root node. This shreds data locality. Meanwhile, if I have a Tree with a ForestID, and I want that Tree's data, I never need to look at the Forest table at all. I can do a direct foreignkey &lt;-> foreignkey join to TreeData. (<code>where Tree.ForestID = TreeData.ForestID</code>).</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USmasonk
    1. USmasonk
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. PORepresenting a many-many and a one-many as a single denormalized view, as a many-to-many, or another way?
      1. This table or related slice is empty.
      1. VTUpMod
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload