Comments table to Posts and Products tables

Question

On a database I have Posts, Products and other tables.
Both posts and products can have comments with same structure.

create table dbo.Posts ( 
  Id int not null
  Title nvarchar (120) not null
)

create table dbo.Products ( 
  Id int not null
  Name nvarchar (120) not null
)

create table dbo.Comments ( 
  Id int not null
  Content nvarchar (2000) not null,
  Created datetime not null
)

create table dbo.PostComment ( 
  PostId int not null,
  CommentId int not null
)

create table dbo.ProductComment ( 
  ProductId int not null,
  CommentId int not null
)

I am using a common Comments table because all have the same columns.

Does this make sense? The other approach would be to have the following:

create table dbo.PostComments ( 
  Id int not null,
  PostId int not null,
  Content nvarchar (2000) not null,
  Created datetime not null
)

create table dbo.ProductComments ( 
  Id int not null,
  ProductId int not null,
  Content nvarchar (2000) not null,
  Created datetime not null
)

I have a few questions:

Which approach makes more sense?
On the first approach how can I guarantee that one Comment is only used on one Post ... And even better, would be used only in one Post or one Product?

if you do not have plans to create FKs, you can create one `Comment` table with `id` and `tableName` columns — Mikhail Lobanov, Apr 21 '17 at 14:27
You could create a single `Comments` table with both `ProductId` and `PostId` columns, then use a trigger to ensure that exactly one is non-NULL in each row. In these cases I usually ponder what will happen as the system grows, e.g. reviews and review comments? Combining them into one table gets clumsier. A separate table for each type of comment will be easier to maintain and more efficient. A search across all of the comments is still easy enough using `union`. — HABO, Apr 21 '17 at 14:34

hastrb · Answer 1 · 2017-04-21T15:04:48.517

1

To guarantee that one comment is only used by one Post, you can create a unique index on a table. So, the solution is to use indexes. On other hand, it is 1-to-1 relation.

CREATE TABLE dbo.Post ( 
  Id INT NOT NULL IDENTITY(1, 1),
  Title NVARCHAR(120) NOT NULL
);

CREATE TABLE dbo.Product ( 
  Id INT NOT NULL,
  Name NVARCHAR(120) NOT NULL
);

CREATE TABLE dbo.Comment ( 
  Id INT NOT NULL IDENTITY(1, 1),
  Content NVARCHAR(2000) NOT NULL,
  Created DATETIME NOT NULL
);

CREATE TABLE dbo.PostComment ( 
  PostId INT NOT NULL,
  CommentId INT NOT NULL
);

CREATE TABLE dbo.ProductComment ( 
  ProductId INT NOT NULL,
  CommentId INT NOT NULL
);

CREATE UNIQUE INDEX IX_PostComment_CommentId ON dbo.PostComment(CommentId);

CREATE UNIQUE INDEX IX_ProductComment_CommentId ON dbo.ProductComment(CommentId);

Also, please, use the singular for the table names.

Hope, it heelps. We also could be interested in what is a relation between Post and Product.

Solution with a single Comment table and two nullable columns for PostId and ProductId + a trigger is a quite tricky solution. I do not recommend you following this way.

EDIT: Post or product can have zero or more comments.

edited Apr 21 '17 at 15:04

answered Apr 21 '17 at 14:32

hastrb

410
4
12

An even better approach would be to add CommentID as a column to the Product and Post tables. The PostComment and ProductComment tables are really rather pointless as the relationship between Post and Comment is 1:1. – Sean Lange Apr 21 '17 at 14:35
The OP wants a comment to apply to a single post or product, but does not say that a post or product can have only one comment. – HABO Apr 21 '17 at 14:38
Why the recommendation to use singular names? Most people in the industry recommend plural names. There isn't a single Comment, this represent a collection of Comments. As such, plural names make sense. :) – Sean Lange Apr 21 '17 at 14:38
@SeanLange Please, read this question. http://stackoverflow.com/questions/338156/table-naming-dilemma-singular-vs-plural-names – hastrb Apr 21 '17 at 14:41
Have you read it? Notice how there is no general agreement? Your suggestion that the user change to singular is your opinion. The best option for this is to be consistent. If you use singular then stick with singular, and the converse is also true. – Sean Lange Apr 21 '17 at 14:44
@SeanLange that's up to you. That's almost philosophy. – hastrb Apr 21 '17 at 14:45
@SeanLange A post can have many comments and product can have many comments but a comment can only be used in one post or one product. – Miguel Moura Apr 21 '17 at 14:47
I agree that makes more sense to allow multiples but I didn't get that from the OP. Perhaps I am misreading it though. :) – Sean Lange Apr 21 '17 at 14:49
@SeanLange From the question: "On the first approach how can I guarantee that one Comment is only used on one Post ... And even better, would be used only in one Post or one Product?". – HABO Apr 21 '17 at 14:53
@Habo that is sufficiently vague enough that it is not immediately clear to me how they want that relationship. But in the only logical way this answer would be how I would want to design it. – Sean Lange Apr 21 '17 at 14:55
@SeanLange The quotation referred the use of a single `Comments` table and two cross reference tables for posts and products. The intent, as I interpret it, was to ensure that a single comment applied to exactly one post _exclusive-or_ product, but not to limit the number of comments on any given post or product. – HABO Apr 21 '17 at 14:59

Comments table to Posts and Products tables

1 Answers1