What is a better way to store status updates in a database?

Question

I'm developing a web application that will enable users to post short status updates similar to Twitter. The only way I can think of storing these posts is to have a large "status_updates" table that stores EVERY user's status updates:

--------------------------------------
| table: status_updates              |
-------------------------------------|
| id | user_who_posted | update_text |
--------------------------------------

This method requires something like this SQL query to get each user's updates:

SELECT * FROM status_updates where user_who_posted="username"

and I think that wouldn't be very inefficient. Is there a better way of doing this?

Answer 1

This actually can be very efficient as long as you properly set up an index for the status_updates table on user.

If you are truly worried about the table becoming very, very large you may want to look into horizontal partitioning of your database(s).

Answer 2

It would be quicker to not have a string as part of your search criteria, and instead have your user replaced with a surrogate key:

SELECT update_text
FROM status_updates
INNER JOIN users
    ON status_updates.user_id = users.user_id
WHERE users.username = 'username'

Obviously, indexing and potentially partitioning your table could be useful for scalability.

Answer 3

Build a user table, and have the user_id be an integer foreign key to that user table. Then, build an index on the user_id field to allow for rapid retrieval.

In short:

status_updates:
--------------------------------------
|  status_id  |  user_id  |  status  |
--------------------------------------
|          1  |        1  |  Woot!   |
--------------------------------------
|          2  |        1  |  Yeah!   |
--------------------------------------
|          3  |        2  |  Hello!  |
--------------------------------------

users:
--------------------------
|  user_id  |  username  |
--------------------------
|        1  |  'Joe'     |
--------------------------
|        2  |  'John'    |
--------------------------

Then, to retrieve, you would do this:

select
    u.username,
    s.status
from
    status_updates s
    inner join users u on
        s.user_id = u.user_id
where
    u.username = 'John'

This will retrieve:

-------------------------
|  username  |  status  |
-------------------------
|  John      |  Hello!  |
-------------------------

Do with that what you will. That will be very performant on millions of rows, so long as you build your indexes right. What RDBMS are you using, so I can point you to the right spot for that?