StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<blockquote> <p>Option #1 ensures that each page belongs to one and only one other entity, although the relationship cant actually be enforced as the parentId field can point to more than one place.</p> </blockquote> <p>Right. In terms of relational theory, the problem is that your "<code>parentId</code>" column violates <a href="http://en.wikipedia.org/wiki/Third_normal_form" rel="nofollow noreferrer">third normal form</a>, because its meaning varies per row, based on the value in <code>parentType</code> (a non-key column).</p> <p>You wouldn't have a properly designed database if a single column could contains someone's phone number <em>or</em> their birthdate, per row, depending on some other flag. Those are two different facts about the person, and they each deserve their own column. Likewise, storing both site_id or node_id in a single column would have the same problem.</p> <p>Another clue that this is a flawed design is that you can't declare a foreign key constraint to point to <em>either</em> of two referenced tables.</p> <blockquote> <p>Option #2 is cleaner, but it's basically saying that the site "belongs" to the two error and not found pages, and that's probably bad practice.</p> </blockquote> <p>I see why you're saying that, because of the <em>belongs to</em> conventions in Rails-like frameworks. But these are conventions; they aren't necessarily the only relationship that foreign keys can model. You can make one entity refer to exactly one other entity, in a <em>has one</em> relationship. In this case, the foreign key reverses direction.</p> <p>I would say it's logically true that the Error page and the Not Found page <em>belong to</em> the site, not the other way around. And the way to make them mandatory is to have another entity reference these pages, and apply the <code>NOT NULL</code> constraint to these references. This is what you've described.</p> <pre><code>CREATE TABLE site ( . . . error_page_id INT NOT NULL, notfound_page_id INT NOT NULL, FOREIGN KEY (error_page_id) REFERENCES pages (page_id), FOREIGN KEY (notfound_page_id) REFERENCES pages (page_id) ); </code></pre> <p>This meets your immediate need, it's enforceable, and it's in Normal Form.</p> <hr> <p>@NXC <a href="https://stackoverflow.com/questions/537782/tricky-relational-db-design-question/537801#537801">suggests</a> making dummy nodes for Error and Not Found pages. Although this allows these nodes to be stored in the node hierarchy, it fails to enforce that a site must have these pages. That is, a site could be stored <em>without</em> references to these nodes.</p> <p>@Tony Andrews <a href="https://stackoverflow.com/questions/537782/tricky-relational-db-design-question/537837#537837">suggests</a> storing two columns in each page, <code>site_id</code> and <code>site_node_id</code>, and adding a CHECK constraint to ensure that exactly one of these is non-NULL. This seems better than the <code>parent_id</code>/<code>parent_type</code> option, but it still doesn't offer any enforcement that every site must have an Error and a Not Found page.</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