Skip to main content
Database Administrators

Return to Answer

added 669 characters in body
Source Link
J.D.
J.D.
  • 39.5k
  • 12
  • 60
  • 134

It just depends on what your use cases are and will be.

From a logical design perspective, it would sound like Comments and Posts should be separate tables. But if you're pretty certain those two objects will be modeled exactly (or near exactly) the same way. Then you can probably implement them as one table.

In reality, especially as requirements change in the future typically, you're generally better off having a single table per object / purpose. So a separate Comments table from the Posts table may arguably be better future proofing.

Keeping them as separate tables (even if they have very similar shapes) may be beneficial for performance and maintenance too, if there are different querying use cases for the objects. For example, you may want to run a query to get the top 3 most recently created Posts for a user, and you might want an aggregative query to get the Tags with the sum of the lowest scored Comments of that user. Completely separate queries that would likely have unrelated indexes on their tables. Separating Posts and Comments into their own tables, allows you to also keep separate those unrelated indexes and maintain them with respect to their tables appropriately.

Also, the relationship between Posts and Comments is typically one-to-many, as a Comment is generally the child to only one single parent Post. If you're following that kind of model, then even as separate tables you wouldn't need a linking table like PostCommentLinker. Instead you can create a foreign key column in the Comments table called PostId that references the parent Post of that Comment.

It just depends on what your use cases are and will be.

From a logical design perspective, it would sound like Comments and Posts should be separate tables. But if you're pretty certain those two objects will be modeled exactly (or near exactly) the same way. Then you can probably implement them as one table.

In reality, especially as requirements change in the future typically, you're generally better off having a single table per object / purpose. So a separate Comments table from the Posts table may arguably be better future proofing.

Also, the relationship between Posts and Comments is typically one-to-many, as a Comment is generally the child to only one single parent Post. If you're following that kind of model, then even as separate tables you wouldn't need a linking table like PostCommentLinker. Instead you can create a foreign key column in the Comments table called PostId that references the parent Post of that Comment.

It just depends on what your use cases are and will be.

From a logical design perspective, it would sound like Comments and Posts should be separate tables. But if you're pretty certain those two objects will be modeled exactly (or near exactly) the same way. Then you can probably implement them as one table.

In reality, especially as requirements change in the future typically, you're generally better off having a single table per object / purpose. So a separate Comments table from the Posts table may arguably be better future proofing.

Keeping them as separate tables (even if they have very similar shapes) may be beneficial for performance and maintenance too, if there are different querying use cases for the objects. For example, you may want to run a query to get the top 3 most recently created Posts for a user, and you might want an aggregative query to get the Tags with the sum of the lowest scored Comments of that user. Completely separate queries that would likely have unrelated indexes on their tables. Separating Posts and Comments into their own tables, allows you to also keep separate those unrelated indexes and maintain them with respect to their tables appropriately.

Also, the relationship between Posts and Comments is typically one-to-many, as a Comment is generally the child to only one single parent Post. If you're following that kind of model, then even as separate tables you wouldn't need a linking table like PostCommentLinker. Instead you can create a foreign key column in the Comments table called PostId that references the parent Post of that Comment.

Source Link
J.D.
J.D.
  • 39.5k
  • 12
  • 60
  • 134

It just depends on what your use cases are and will be.

From a logical design perspective, it would sound like Comments and Posts should be separate tables. But if you're pretty certain those two objects will be modeled exactly (or near exactly) the same way. Then you can probably implement them as one table.

In reality, especially as requirements change in the future typically, you're generally better off having a single table per object / purpose. So a separate Comments table from the Posts table may arguably be better future proofing.

Also, the relationship between Posts and Comments is typically one-to-many, as a Comment is generally the child to only one single parent Post. If you're following that kind of model, then even as separate tables you wouldn't need a linking table like PostCommentLinker. Instead you can create a foreign key column in the Comments table called PostId that references the parent Post of that Comment.