StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>I'd go with the Posts approach. This is the best way to ensure referential integrity.</p> <p>If you need additional columns for Answers and Questions respectively, put them in additional tables with a one-to-one relationship with Posts. </p> <p>For example, in MySQL syntax:</p> <pre class="lang-sql prettyprint-override"><code>CREATE TABLE Posts ( post_id SERIAL PRIMARY KEY, post_type CHAR(1), -- must be 'Q' or 'A' -- other columns common to both types of Post UNIQUE KEY (post_id, post_type) -- to support foreign keys ) ENGINE=InnoDB; CREATE TABLE Comments ( comment_id SERIAL PRIMARY KEY, post_id BIGINT UNSIGNED NOT NULL, -- other columns for comments (e.g. date, who, text) FOREIGN KEY (post_id) REFERENCES Posts(post_id) ) ENGINE=InnoDB; CREATE TABLE Questions ( post_id BIGINT UNSIGNED PRIMARY KEY, post_type CHAR(1), -- must be 'Q' -- other columns specific to Questions FOREIGN KEY (post_id, post_type) REFERENCES Posts(post_id, post_type) ) ENGINE=InnoDB; CREATE TABLE Answers ( post_id BIGINT UNSIGNED PRIMARY KEY, post_type CHAR(1), -- must be 'A' question_id BIGINT UNSIGNED NOT NULL, -- other columns specific to Answers FOREIGN KEY (post_id, post_type) REFERENCES Posts(post_id, post_type) FOREIGN KEY (question_id) REFERENCES Questions(post_id) ) ENGINE=InnoDB; </code></pre> <p>This is called Class Table Inheritance. There's a nice overview of modeling inheritance with SQL in this article: "<a href="http://everything2.com/title/Inheritance%2520in%2520Relational%2520Databases" rel="noreferrer">Inheritance in relational databases</a>."</p> <p>It can be helpful to use post_type so a given Post can be only one answer or one question. You don't want both an Answer and a Question to reference one given Post. So this is the purpose of the <code>post_type</code> column above. You can use CHECK constraints to enforce the values in <code>post_type</code>, or else use a trigger if your database doesn't support CHECK constraints.</p> <p>I also did a presentation that may help you. The slides are up at <a href="http://www.slideshare.net/billkarwin/sql-antipatterns-strike-back" rel="noreferrer">http://www.slideshare.net/billkarwin/sql-antipatterns-strike-back</a>. You should read the sections on Polymorphic Associations and Entity-Attribute-Value.</p> <hr> <p>If you use Single Table Inheritance, as you said you're using Ruby on Rails, then the SQL DDL would look like this:</p> <pre class="lang-sql prettyprint-override"><code>CREATE TABLE Posts ( post_id SERIAL PRIMARY KEY, post_type CHAR(1), -- must be 'Q' or 'A' -- other columns for both types of Post -- Question-specific columns are NULL for Answers, and vice versa. ) ENGINE=InnoDB; CREATE TABLE Comments ( comment_id SERIAL PRIMARY KEY, post_id BIGINT UNSIGNED NOT NULL, -- other columns for comments (e.g. date, who, text) FOREIGN KEY (post_id) REFERENCES Posts(post_id) ) ENGINE=InnoDB; </code></pre> <p>You can use a foreign key constraint in this example, and I recommend that you do! :-)</p> <p>Rails philosophy tends to favor putting enforcement of the data model into the application layer. But without constraints enforcing integrity at in the database, you have the risk that bugs in your application, or ad hoc queries from a query tool, can harm data integrity. </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