StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>I would call this a data dependency. Not all data dependencies can be modeled directly or conveniently with relational decomposition. This one can be handled pretty easily with a check constraint:</p> <pre><code>CREATE TABLE Students ( id SERIAL PRIMARY KEY, -- for example, something else in reality grade INTEGER NOT NULL, honors BOOLEAN, CONSTRAINT ensure_honors_grade CHECK((honors IS NULL AND grade < 7) OR (honors IS NOT NULL AND grade >= 7)) ); </code></pre> <p>Another solution might be to use two tables:</p> <pre><code>CREATE TABLE Students ( id SERIAL PRIMARY KEY, grade INTEGER NOT NULL, CONSTRAINT id_grade_unique UNIQUE (id, grade) -- needed for FK constraint below ); CREATE TABLE Honors ( student_id INTEGER NOT NULL, grade INTEGER NOT NULL, honors BOOLEAN NOT NULL, CONSTRAINT student_fk FOREIGN KEY (student_id, grade) REFERENCES Students(id, grade), CONSTRAINT valid_grade CHECK(grade >= 7) ); </code></pre> <p>This alternative design is more explicit about the relationship between the grade and whether or not there is an honors flag, and leaves room for further differentiation of students in grades 7-8 (though the table name should be improved). If you only have the one property, the honors boolean, then this is probably overkill. As @BrankoDimitrijevic mentions, this doesn't enforce the existence of a row in <code>Honors</code> just because the grade is 7 or 8, and you're also paying for an index you wouldn't otherwise need. So there are tradeoffs; these are certainly not the <em>only</em> two designs possible; Branko also suggests using triggers.</p> <p>When it comes to OO design, @Ryan is correct, but for proper relational database design one does not, in general, approach problems by trying to identify inheritance patterns. That is the OO perspective. It will always be important to concern yourself with your access patterns and how your code will be getting at the data, but in relational database design, one strives for normalization and flexibility in the database first and the code second, because there will invariably be multiple codebases getting at the data and you want to ensure the data is always valid no matter how buggy the accessing code is.</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