Help with MySQL Friends List

I am making an Android app that will use a friends list. As far as I know, you can't store an array in a MySQL table, so it seems that I will have to make a separate table from my "Users" table and call it, say, "Friends". This table will just have Friend1 and Friend2.

My question is the preferred way to store this table. I can use the UserName field (string) from my "U开发者_如何学编程sers" table to store them, or I could use the UserID field (integer) from my "Users" table. Using the ID would make the table smaller because the small integers take up more space than the string, but at the same time, I access this data mainly using the UserName field (so I have to query the Users table to get the UserID from the Users table).

Which method is preferred for a MySQL table? Using the users name directly so I do not have to find the UserID from the Users table, or saving the table as two integers, and querying to find the ID from the UserName? Thanks.

---

Store two userID keys from the users table.

Let's say that you change a name of a contact from "Guy from bar" to "Mr. McMoneypants". If this contact was a friend, it will still show up as "Guy from bar" even after the change.

Try to keep data from living in multiple places.

---

The preferred method is to use the UserID from the Users table in the Friends table as a way to reference that user. That way, as Phillip says, the User can change their name and you only have to change it in one place. Plus, as you say, your Friends table will take up less space with a numeric column as compared to a string column.

And in regards to "(so I have to query the Users table to get the UserID from the Users table)", the following query is not too cumbersome:

SELECT FriendName
FROM Users Natural Left Join Friends
WHERE UserName = 'Ralph';

As far as your query is concerned, you never had to deal with the UserID column. That's not that much harder than your method:

SELECT FriendName
FROM Friends
WHERE UserName = 'Ralph';