I'm designing a database for a web site that will have at least 4 different object types represented (articles, blog posts, photos, stories), each of which have different enough data requirements to warrant their own tables. We want users to be able to post comments for any of these types. The data requirements for comments are simple and independent of the type of thing the comment regards (ie just a comment body, and the author's email).
I want to avoid the redundancy of creating and managing 4+ separate tables for the comments, so I'd like to be able to hold all comments in one table, possibly specifying the relation via 2 columns: one to designate the parent entity and one for the parent row Id.
but I don't understand how, then, I would implement foreign keys, since foreign keys establish a relation between 2 and only 2 tables (right?).
So with all that in mind, what would be the best approach?
Snake: It's not a bad idea, but it's not necessary for joins, and you don't need dynamic SQL. (Dynamic SQL has risks that are better avoided if possible.) A supertype/subtype design for 'n' subtypes maps to 'n'+1 base tables and 'n' views. Each of the 'n' views joins the supertype table to one of the subtype tables; then clients use the views, not the base tables. (Write triggers, if necessary, to let you do INSERT, UPDATE, DELETE through the views.) Once you have a well-named, updatable view, you don't need to read that column yourself. It's just to help SQL maintain data integrity.
Catcall: Thanks. Gotcha clear on the use of views. I kinda knew that but didn't think thru the question well enough. Another question: My plan would be to rely on the Publications table to generate the Pub_Id -- then this gets copied to the sub-type table, therefore the value will be unique within each table, so I wouldn't need to have a compound primary key on Publications table. Does that make sense?
Snake: I understand what you're saying, but you really need that column and the compound key if you're using a SQL dbms. That CHAR(1) column, implemented in the supertype and every subtype and used as part of both a compound key and a foreign key reference, guarantees that each row in the supertype table can join to one and only one row in one and only one subtype table. Without it, you could insert the same pub_id in every subtype table, which makes the supertype/subtype design useless.
CatCall: So back to the last question: given the role pubtype has in the primary key, is there a performance reason, or other, to keep it to 1 character? And if so, would an int be even better? (BTW: thanks for your time and patience. And pls excuse my "density" on these things. My experience is primarily front-end, but I'm a one-man project this time; though, fortunately, I've lobbied successfuly for lots of time so I can think things thru carefully.)
Snake: CHAR(1) is long enough to be readable enough, and it takes less space than an integer. You might get marginally better performance with CHAR(1), or you might get marginally better performance with an integer. But I generally prefer text to numbers, because I find it easier to read text. (A)rticle, (B)log post, (S)tory is easier to remember than (1) article, (2) blog post, (3) story. I don't mind spending time; you shouldn't mind upvoting every one of my replies. And my original answer, too. ;)
Do you mean create a sub-type for images, video notes rather than super-type?
This is the design I would recommend. The last possibility (and whether you need something like this would depend upon your design) would be to add a UserID to each entry in either the comment table or the blog/Video/Etx tables, such the one could retrieve all comments for a specific user (again, if this fits your design requirments, and with some possible adjustments for specific design requirements . . . ). Where the USerID ended up would probably require some thought.