StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>If a given Test is done/performed/created only because of a Visit, and only because of <em>that</em> visit, then add VisitId as a foreign key to the Test table, to indicate the parent/owning/defining Viist.</p> <p>Types/Subtypes are much trickier.</p> <p>If TestABC is a subtype of Test, then in TestABC, the primary key should be TestId, and it should also be a foreign key to table Test column TestId. [<em>I never use just “ID” for surrogate primary key columns – it gets too confusing too fast when writing complex joins.</em>]</p> <p>Sounds like there are lots of Test subtypes. Sounds also like they’re exclusive, e.g. a Test with data in table TestABC cannot also have data in table TestXYZ, or any other subtype table. To determine which type of Test it is, you’d have to query each subtable to find what kind of test it is. That, however, is just awful, so instead add a TestType column to the Test table. Probably best to have a lookup table as well (TestType), with foreign keys to ensure no wayward test types sneak into the system.</p> <p>A subtle issue is how to prevent data from being entered into multiple subtypes tables for a given Test. To guarantee accuracy is perhaps overly-fussy, but if you consider peace of mind and relational integrity as worth the price, do this:</p> <ul> <li>Add a TestType column to each subtype table</li> <li>Add a check constraint to each subtype table such that the TestType column can <em>only</em> be set to the appropriate code for that subtype</li> <li>In the Test table, build the primary key on {TestId, TestType}</li> <li>In each subtype table, build a foreign key on {TestId, TestType} back to table Test (on the two columns) </li> <li>Voila, you can only add a row to a subtype table if the "owning" Test entry is set with that table's TestType.</li> </ul>

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