Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
cmaso
Starting Member
5 Posts |
 Posted - 2008-11-04 : 11:37:25
|
| Just wanted to bounce off my solution to a problem at work. I have to alter my DB structure slightly because of a change in a blog site I'm the web developer for. Right now, I have an "entries" table which holds the entries people make in their blogs, and then a "comments" table which holds the comments which people make for different entries. So each comment has its own uuid, and also has an "entryUUIDfk" foreign key to identify the entry to which it belongs.I've been tasked with making it so that users can now make comments to other comments. I'm thinking the simplest way to handle this is to rename entryUUIDfk to something like "parentUUID" and use that column to store the uuid of the entry or comment to which the comment belongs. Of course, now that uuid might be in the comments table, or it might be in the entries table. To deal with this, I'm thinking I can just use conditional SQL in my "getParent" queries - say "if not exists (select uuid from entries) then select uuid from comments", and figure out a way to have the query also return which table it found the uuid in.Which makes me a little suspicious because it sounds too simple. Any advice/comments?Thanks,Christophe Maso |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-11-04 : 11:47:17
|
| i think you should have parentcommentid in comments table in addition of current columns. you dont need to replace entryUUIDfk as the comments needs to be still linked to entries. so i think the relationship will be entry->comments from comments corresponding to an entry and then comments->comments by means of commentid,parentcommentid to designate comments on comments. |
 |
|
|
cmaso
Starting Member
5 Posts |
Posted - 2008-11-04 : 12:10:48
|
| Ah - and have the value of parentCommentID (or whatever) be null if the comment belongs to no other comment. Yes, that's even simpler, and it's certainly handy not to have to query up the chain every time to find the comment's entry, or whether its parent is another comment or the entry itself. And, it won't cause other functions using the comments table to break while I'm coding the changes. Thanks for your thoughts!Christophe |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-11-04 : 12:13:44
|
| Yup..Exactly...and even when you want to get hierarchy of comments you can make use of recursive cte or any other recursive approach queries to retrieve them. |
|
|
|
|
|
|
|
|
Requesting advice on Foreign Key issue