2

So I currently have two entities for which comments are possible, say Pic and Text.

I was looking at this question for a possible DB design: Implementing Comments and Likes in database There we'd have a single Comment table:

#comment_id
#entity_id

Now, my client, which is tech-savvy as well, doesn't like the idea of having a common super class for commentable entities - for whatever reason I don't know.

So I currently have a Comment table which has a relationship with either Text and Pic (I am doing reasonably ok at DBs but am not an expert). This would mean that I'd have a Comment table with:

#comment_id
#pic_id
#text_id

which would result, for comments on pics, to have to query where pic_id is not null (the same way for text comments, query for text_id != null). This feels rather odd to me (nevertheless, the queries we'd probably have more are "get_comments_for_pic", or "get_comments_for_text", which would entail querying by pic_id or text_id).

But the client is pushing to have separate tables for each Text_Comment, and Pic_Comment, which would basically achieve the same but would better separate the different comments.

Is there any other reason to prefer one over the other which I can't see right now? Any other suggestion on how to implement this?

Community
  • 1
  • 1
transient_loop
  • 5,984
  • 15
  • 58
  • 117

1 Answers1

1

Your client is right. Your way, your DB table will have many NULL entries, which usually is a sign of bad DB design. The size of each row will be larger. Also, you will require two indexes in fields with too many NULL values and the DB table will be double the size, than splitting into two DB tables, also increasing the size of the corresponding indexes.

Overall, with your client suggestion, smaller rows, smaller DB tables, smaller indexes => faster performance.

Alexandros
  • 2,160
  • 4
  • 27
  • 52