Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>Even if you could put the identity sequence across multiple tables, your comment table is not going to be able to reference both columns in a single foreign key.</p> <p>The best way to do this, in terms of relational database design theory, would be to create two comment tables. But obviously, you want to avoid that, probably for code-reuse reasons.</p> <p>The most straightforward pragmatic approach would be to put two foreign key columns on the comment table, and just make one null and the other not null for each comment.</p> <p>Another approach, which might be the best compromise, is this. You refer in your question to an "entity ID". So make an Entity table! Then the authors and books and comments can all refer to <em>that</em> table.</p> <p><strong>Edited to add:</strong></p> <p>Philip Kelley, Ray, and (I think) Artic have all suggested modifying the comment table by adding an <code>entity_id</code>, which can refer to either the <code>book_id</code> or the <code>author_id</code>, and a flag of some sort (<code>char(1)</code>, <code>tinyint</code>, and <code>boolean</code>, respectively) that indicates which of these is being referred to.</p> <p>This is not a good solution for many reasons, both pragmatic (including data integrity, reporting, efficiency) and theoretical.</p> <p>The first and most obvious problem is the data integrity problem. A relational database system should always be responsible for maintaining the integrity of its own data, and there are natural and preferred ways that the DB is designed to do this. One of the most important of these mechanisms is the foreign key system. If the <code>comment.entity_id</code> column is to reference both <code>book.book_id</code> and <code>author.author_id</code>, then a foreign key cannot be created for this column. </p> <p>Sure, you could put a check in your DML (insert, update, delete) stored procedures to verify the references, but that would quickly turn into a big mess, as all DML operations on all three tables would be involved.</p> <p>And that leads us to the efficiency problem. Whenever a query is run against the <code>comment</code> table, it will require joins to either the <code>author</code> or <code>book</code> table or both. The query plan generation system will not have foreign keys available to optimize with, so its performance could very well be degraded.</p> <p>Then there are problems with this scheme in reporting. Any report generating system is going to have trouble with this sort of system. Sure this won't be a problem for expert programmers, but any user ad-hoc reports are going to have to mock up the logic behind when the <code>event_id</code> means this or that, and it could be a pretty bad deal. Maybe you won't ever use report generating tools on this database. But then again, nobody knows where a database is going to be ultimately used. Why not work with the system to allow for anything?</p> <p>And that leads us to the theoretical problems.</p> <p>In relational database theory, each row (a.k.a. "tuple") in each table ("relation variable") represents a proposition about the real world. Designing a table is to decide the form of that proposition. Let's look at a few examples of how this might work.</p> <pre><code>comment (comment_id int, comment_type char(1), entity_id int, user_id int, comment_text nvarchar(max), comment_date datetime) /* comment_id identifies a comment (comment_text) that a user (user_id) has made about a book (entity_id if comment_type = 'B') or author (entity_id if comment_type = 'A') at a particular date and time (comment_date).*/ </code></pre> <p>Here it is clear that the column (or "attribute") called <code>entity_id</code> is doing double-duty. It doesn't really represent anything, except with reference to another column. This is workable, but unsatisfactory.</p> <pre><code>comment (comment_id int, book_id int, author_id int, user_id int, comment_text nvarchar(max), comment_date datetime) /* comment_id identifies a comment (comment_text) that a user (user_id) has made about a book (book_id if not null) or author (author_id if not null) at a particular date and time (comment_date). */ </code></pre> <p>This buys us the foreign keys that are the biggest omission from the first version. But this still isn't terribly satisfactory, unless a single comment can refer to both a book and an author (which might be reasonable). Nullable columns are a warning sign that something is wrong with the design, and that may be the case here as well. A check constraint may be necessary to avoid a comment that refers to nothing at all, or to both a book and an author if that is not to be allowed.</p> <p>From a theoretical perspective (and thus, my perspective :)) there is a clear best option:</p> <pre><code>book_comment (book_comment_id int, book_id int, user_id int, comment_text nvarchar(max), comment_date datetime) /* book_comment_id identifies a comment (comment_text) that a user (user_id) has made about a book (book_id) at a particular date and time (comment_date). */ author_comment (author_comment_id int, author_id int, user_id int, comment_text nvarchar(max), comment_date datetime) /* author_comment_id identifies a comment (comment_text) that a user (user_id) has made about an author (author_id) at a particular date and time (comment_date). */ </code></pre> <p>This last option would provide the best efficiency, data integrity, and ease of reporting. And the only expense would be that the DML stored procedures would need to put the comments into the right tables, which is not a big deal, since they had to know what the comments were referring to anyway.</p> <p>If your plan was to retrive all comments for a book or author at once, then you can easily create a view on top of these tables that reproduces the other designs, if that's what you want to do.</p> <pre><code>create view comments as select book_comment_id as comment_id, book_id as entity_id, comment_text, 'B' as comment_type from book_comment union select author_comment_id as comment_id, author_id as entity_id, comment_text, 'A' as comment_type from author_comment </code></pre>
    singulars
    1. This table or related slice is empty.
    1. POCan I make an identity field span multiple tables in SQL Server?
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USJeffrey L Whitledge
    1. USJeffrey L Whitledge
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COHi Jeffrey, Why do you think it is a bad idea to use a "commenter type ID" along with the "Entity ID"? Why are your two latter suggestions better? I'm still learning! Thanks.
      singulars
      1. PO
      1. USjohnnycakes
    2. COOK. I addressed your question in an edit to the answer.
      singulars
      1. PO
      1. USJeffrey L Whitledge
    3. COThis, IMO, is the correct answer. Using an EAV structure for this type of solution is the wrong answer and will turn ugly in reporting. Adding another table really does not cost much but provides many benefits including the ability for author comments to later have attributes that book comments do not.
      singulars
      1. PO
      1. USThomas
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

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