I'm creating a simple private message system and I'm no sure which database design is better.
The first design is a table for messages, and a table for message comments:
Message
---------------
id
recipientId
senderId
title
body
created_at
MessageComment
---------------
id
messageId
senderId
body
created_at
the second design, is one table for both messages and comments, and an addition field messageId so i'll be able to chain messages as comments.
Message
---------------
id
recipientId
senderId
messageId
title
body
created_at
I'd like to hear your opinion!
In this case, I'd vote for one table.
In general, whenever the data in two tables is the same or very similar and the logical concepts they represent are closely related, I'd put them in a single table. If there are lots of differences in the data or the concepts are really different, I'd make them two tables.
If you make two tables and you find yourself regularly writing queries that do a union of the two, that's an indication that they should be combined.
If you make one table but you find there are many fields that are always null for case A and other fields that are always null for case B, or if you're giving awkward double-meanings to fields, like "for type A this field is the zip code but for type B it's the product serial number", that's an indication they should be broken out.