2

I have several tables that will represent an object that can be commented on. I would like to store all the comments in one table. To relate the other tables to the comments, I was thinking of adding a CommentParentId column to each one. That column would need to be unique across all the tables that are related to the comments table, so I was thinking of creating a table, CommentParents, that would simply have an Id column in order to have the DB handle generating the Ids. For instance:

Comments

Id
Title
Body
User
Date
CommentParentId

Posts

Id
Title
Body
User
Date
CommentParentId

Images

Id
Title
User
File
Date
CommentParentId

Users

Id
Name
Email
DateJoined
CommentParentId

CommentParents

Id

So that table would simply be used to create new Ids. Is there another way to do this? What are the advantages?

I'm using SQL Server 2014

1 Answer 1

5

You could utilize a sequence object to facilitate the mechanism of generating the IDs if that is the sole purpose of the CommentParents table. Take a look at the TechNet documentation for the TechNet SQL Server 2014 Sequence Documentation for more detailed information. What this sequence object will do that your current table can't do is allow you to grab a value and assign it to a variable ahead of time without worrying about doing an INSERT/SELECT SCOPE_IDENTITY() process. The advantage here is at great scale the current design you have would break down due to the need to maintain metadata overhead where SQL Server has caching for Sequence objects.

1
  • That was exactly what I was looking for. Thanks! Oct 15, 2015 at 6:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.