Numbering comments in ASP.NET and MSSQL

Question

I've just thought about best way to store comments in database with appropriate numbers according to the article.

The idea is to store comments with composite primary key (commentId, articleId) where commentId is generated according to the given articleId. The system of generating should has same principle as IDENTITY generated columns in SQL Server, because if someone delete the comment, the number will be never used again. I guess there is not any functionality in Microsoft SQL Server to do that with composite PK, so I am asking about some replacement for this solution.

First thought was to use transaction to get MAX(commentId) + 1, but I am looking for something more abstract (maybe INSTEAD OF trigger), something that could be used for example in LINQ with no knowledge of the background, just insert to the appropriate table all required values (so no commentId) and save it.

Thank you for all responses, Jan.

Answer 1

I would use an identity column as the key for the comments, why do you need a numbering for the comments stored in the database?

Answer 2

I would use an autogenerated identity column for the commentId and have it be the primary key alone. I'd create an index on the articleId for look ups. I would also have createdDate column that is autopopulated with the current date on insertion -- mark it as db generated and readonly in LINQ so it doesn't require or try to insert/update the value. To get a numbering -- if showing them by date isn't enough -- I'd order by createdDate inversed and assign a numeric value in the select using Row_Number() or a numbering on the client side.

Answer 3

Thank you for responses, I wanted something with numbered comments because of referencing in the text of comments. I did not want to make reaction by names, sometimes one person reacts more times, so with this system, I will know to which one the person is replying.

So today I made up this INSTEAD OF INSERT trigger:

CREATE TRIGGER InsertComments ON Comments
INSTEAD OF INSERT
AS
DECLARE @Inserted TABLE
(
    ArticleId INT NOT NULL,
    UserId INT NOT NULL,
    CommentDate DATETIME NOT NULL,
    Content NVARCHAR(1000) NOT NULL,
    RowNumber INT NOT NULL
)
INSERT INTO @Inserted
SELECT ArticleId, UserId, CommentDate, Content, ROW_NUMBER() OVER (ORDER BY CommentDate) AS RowNumber
FROM INSERTED
DECLARE @NumberOfRows INT = (SELECT COUNT(*) FROM @Inserted)
DECLARE @i INT = 1
WHILE (@i <= @NumberOfRows)
BEGIN
    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
    BEGIN TRAN
    DECLARE @CommentId INT = (SELECT ISNULL(MAX(CommentId), 0)
     FROM Comments WHERE ArticleId = (SELECT ArticleId
      FROM @Inserted WHERE RowNumber = @i)) + 1
    INSERT INTO Comments(CommentId, ArticleId, UserId, CommentDate, Content)
    SELECT @CommentId, ArticleId, UserId, CommentDate, Content
    FROM @Inserted WHERE RowNumber = @i
    COMMIT
    SET @i = @i + 1
END

I know this is not the perfect solution, but it works exactly how I needed. If any of you has some comments, I'll be happy to read them.