CODEWITHSUNDEEP
mysql
user291701
user291701

Reputation: 39691

A table of friends - store user ids or usernames?

I have a pretty typical user table setup for my web application:

user_id  |  username
--------------------
    0          greg
    1          john
   ...         ...

Both fields are indexed and unique, so I can look up a user by id or username quickly.

I want to keep a friends table, and am not sure whether to store the user_id values or usernames in that table:

user_id_1   |   user_id_2
--------------------------

            or

username_1  |   username_2
--------------------------

I am going to want to get a list of friends for a user, so it would be convenient to immediately have the usernames in the friends table instead of doing a join on the users table:

select * from friends where username_1 = 'greg';

If I'm storing user ids, I need to do a join then to get the usernames - will that be costly?:

select * from friends 
    where user_id_1 = x 
    join users where user_id = { all those results }

Using user ids allows me to let users change usernames flexibly, but I'm not letting them do that anyway. Any advice would be great.

Thanks

Upvotes: 0

Views: 231

Answers (3)

MigDus
MigDus

Reputation: 767

The ID will be smaller if you use numeric values. Also the index search will be faster. Here you'll find the data types for MySQL 5.0.

Also I don't know how are you using index, but I'd recommend to add and auto-increment field. You can do that to a table, for an integer index like this:

ALTER TABLE `Database`.`tableName` ADD COLUMN `indexName` INTEGER  NOT NULL AUTO_INCREMENT

Upvotes: 1

Anthony Accioly
Anthony Accioly

Reputation: 22471

Well, as you said, using id semantics means you can change the username without having to deal with cascading effects. For most cases PK / UNQ + FK indexes will make joins thundering fast, but you may have a point for huge tables (for which you will eventually need some kind of external index, or other tool anyway).

Upvotes: 1

Nik
Nik

Reputation: 7273

A join on the IDs won't be too bad. The ID may be smaller to store on disk. Also, I would imagine a list of friends would have something other than just user names, in which case, you have to join no matter what.

Upvotes: 1

Related Questions