DB Design question: Tree (one table) vs. Two tables for tweets and retweets?

Question

Hi

I've heard that on stackoverflow questions and answers are stored in the same DB table.

If you were to build a twitter like service that only would allow 1 level of commenting. ie 1 tweet and then comments/replies to that tweet but no re-comments or re-replies.

would you use two tables for tweets and retweets? or just one table where the field parent_tweet_id is optional?

I know this is an open question, but what are some advantages of either solutions?

Answer 1

Retweets are still normal tweets as well. So one table. You wouldn't want to have to load from two tables to include the retweets.

Answer 2

Advantages of one table:

• You can search through all tweets and comments in a simple way.
• You can use one identity column easily for all posts.
• Every post has the same set of columns.

Advantages of two tables:

• If it's more common to search or display only top-level tweets instead of tweets + comments, the table of tweets is that much smaller without comments.
• Two tables can have different sets of columns, so if there are columns meaningful for one type of post but not the other, you can put these columns in the respective table without having to leave them null when not applicable.
• Indexes can also be different on two tables, so if you have the need to search comments in different ways, you can make indexes specialized to that task.

In short, it depends on how you use the data, not only how it's structured. You haven't said much about the operations you need to do with the data.

Answer 3

Like all design questions, it depends.

I don't normally like to mix concepts in a single table. I find it can quickly damage the conceptual integrity of the database schema. For example, I would not put posts and replies in the same table because they are different entities.