This may seem strange, but I am wondering if it is possible to have a MySQL table have a column that can contain a list of values. For instance, say I have a table that represents a friends list like facebook, how can I simulate this in a table? I'm thinking that you could add the usernames into an attribute column but not sure that is the best idea or even how to do that. Any suggestions on how to achieve this or an alternative?
Add a comment
|
3 Answers
Under certain circumstances you could use the SET type, which is similar in functionality to the ENUM type. It allows you to store one or more predefined values in a field. However, for the facebook friends case it would not be practical as each time a new user is created the column definition would require updating.
Wolfram's suggestion of a mapping table is definitely the better solution as it also enables the use of foreign key constraint which will ensure referential integrity when one user is deleted (assuming you use cascading). Also, if you ever need to use the relationship in a JOIN then the mapping table is the only solution.
Comments
So what you're looking for, in keeping with the facebook example, is to have a table with two columns. The first, identifies the user, and the second a list of that user's friends?
As far as I know, you can't treat a column like an array. You could have a string containing all the individual names separated by dashes, but that would be un-robust.
My suggestion would be to have a friends table which has two columns, both of which are varchars or strings. Each column contains the name or id of 1 person only, which denotes a friendship between those two people.
Then, if you wanted a list of foobar's friends, you would just query:
SELECT *
FROM friends
WHERE user_a == 'foobar' || user_b == 'foobar'
Now, this would actually give you both columns, one of which is foobar and one of which is his/her friend. So you might have to get a little creative as to separating it into just foobar's friends, but I'm sure you can figure out a way that works for your code.
1 Comment
James Brown
I guess I'll probably just make a table for each user that logs in so they can just add the "friends" to the table and to show all their "friends" I could just use "SELECT names FROM friends". Maybe not the ideal way to do so, but seems easiest.
While this isn't usually a great idea, MySQL does have this:
FIND_IN_SET('b','a,b,c,d')
SELECT ... FROM friends WHERE FIND_IN_SET('foo',friends_list) > 0;
So you could do what you asked very easily. It's just not typically suggested.
