Search code examples
mysqltwitterdatabase-designtreedatabase-schema

Twitter-like database table structure

I am trying to create a database that can be used like Twitter works. That is:

  1. Treestructure: Any node can have multiple childnodes.
  2. All nodes have a timestamp

Criteria 1 and 2 suggests a table structure based on basic columns something like:

NodeID        (int)
ParentNodeID  (int)
UserID        (int)
TS            (TimeStamp)
MSG           (varchar)

When viewing any node (n) all parent nodes until and including root should be selected, that is easy using the ParentNodeID pointer.

Here comes the caveat: In addition to the parent nodes all child nodes from the current node (n) should also be selected in Chronological order (based on TS) from the table. All child nodes, no matter what child-branch, that belongs to the subtree where (n) is the root.

How do I best (better) structure the table for such queries?

Solution

  • You should take look at how Twitter have been evolving, and check if your use case is similar enough.

    A good start could be this article with database schema examples: https://web.archive.org/web/20161224194257/http://www.cubrid.org/blog/dev-platform/decomposing-twitter-database-perspective/