标签云

微信群

扫码加入我们

WeChat QR Code

数据库设计的文章,博客,照片,故事

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.

2018年05月23日02分53秒

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?

2018年05月23日02分53秒

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.

2018年05月23日02分53秒

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.)

2018年05月23日02分53秒

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. ;)

2018年05月23日02分53秒

Do you mean create a sub-type for images, video notes rather than super-type?

2018年05月24日02分53秒

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.

2018年05月24日02分53秒