I'm integrating the ejabberd friends roster MySQL schema into our platform. I'm going to have to rewrite alot of queries anyway so I have an opportunity to rework the schema if I so desire. I don't often stray as far down the stack to do DB work so I could use a little advice.

I've noticed that subscription states are stored in a CHAR(1) column like so:

N - No subscription
T - Subscription to
F - Subscription from
B - Both

This sets my spider sense tingling, but I don't really know why. It could be that I've just read to many "Normalise Everything!" articles and this is actually fine for the use case. Experts, does this design seem ok to you?

Like a say my database knowledge is both rusty and basic so I'm interested in any advice on how you would tackle this.

--Further non-essential reading for those who are interested--

If you answered no to the above then I guess there's a slew of side questions: From a performance perspective is it better to break out these subscription states into another table or keep the table intact? Is it worth sacrificing some performance to keep the table human readable? And so on.

Also here's the original schema I'm working from. I've already changed that horrible key set up so you don't need to complain about that :)

+--------------+--------------+------+-----+-------------------+-------+
| Field        | Type         | Null | Key | Default           | Extra |
+--------------+--------------+------+-----+-------------------+-------+
| username     | varchar(250) | NO   | MUL | NULL              |       | 
| jid          | varchar(250) | NO   | MUL | NULL              |       | 
| nick         | text         | NO   |     | NULL              |       | 
| subscription | char(1)      | NO   |     | NULL              |       | 
| ask          | char(1)      | NO   |     | NULL              |       | 
| askmessage   | text         | NO   |     | NULL              |       | 
| server       | char(1)      | NO   |     | NULL              |       | 
| subscribe    | text         | NO   |     | NULL              |       | 
| type         | text         | YES  |     | NULL              |       | 
| created_at   | timestamp    | NO   |     | CURRENT_TIMESTAMP |       | 
+--------------+--------------+------+-----+-------------------+-------+

Originally asked by: Ollie Edwards on Stack Overflow