StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Here are a couple options:</p> <p>If it is just the hierarchy above you are modeling, and there is no other data involved, then you can do it in two tables:</p> <p><img src="https://i.stack.imgur.com/hafNI.png" alt="two table model"></p> <p>One problem with this is that you do not enforce that, for example, a <code>sub_group</code> must be a child of a <code>lab_group</code>, or that a <code>component</code> must be child of either a <code>sub_group_1</code> or a <code>sub_group_2</code>, but you could enforce these requirements in your application tier instead.</p> <p>The plus side of this approach is that the schema is nice and simple. Even if the entities have more data associated with them, it might still be worth modeling the hierarchy like this and have some separate tables for the entities themselves.</p> <p>If you want to enforce the correct relationships at the data level, then you are going to have to split it out into separate tables. Maybe something like this:</p> <p><img src="https://i.stack.imgur.com/etRMJ.png" alt="mutiple tables"></p> <p>This assumes that each <code>sub_group_1</code> is only related to a single <code>lab_group</code>. If this is not the case then add a link table between <code>lab_group</code> and <code>sub_group_1</code>. Likewise for the <code>sub_group_1</code> -> <code>sub_group_2</code> relationship.</p> <p>There is a single link table between <code>component</code> and <code>sub_group_1</code> and <code>sub_group_2</code>. This allows a single <code>component</code> to be related to several <code>sub_group_1</code> and <code>sub_group_2</code> entities. The fact it is a single table means that a lot of the <code>sub_group_1_id</code> and <code>sub_group_2_id</code> records will be <code>null</code> (like you mentioned in your question). You could prevent the nulls be having two separate link tables:</p> <ul> <li><code>sub_group_1_component</code> with a foreign key to <code>sub_group_1</code> and a foreign key to <code>component</code></li> <li><code>sub_group_2_component</code> with a foreign key to <code>sub_group_2</code> and a foreign key to <code>component</code></li> </ul> <p>The reason I didn't put this in the diagram is that for me, having to query two tables rather than one to get all the <code>component</code> -> <code>sub_group</code> relationships is too much of a pain. For the sake of a little denormalisation (allowing a few <code>nulls</code>) it is much easier to query a single table. If you find yourself allowing a lot of <code>null</code>s (like a single link table for the relationships between all the entities here) then that is probably denormalising too much.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

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