How to store a list of users, and let the users know they are on that list

Question

Im building a site where a user can add people to their "list". This list belongs to the user, but im wondering whats the best way to store/retrieve/loop through this list in a function, to show the people who are on the list, that they are there and who created it.

So once the list of usernames is created, i would assume store it in a mysql table field, then write a function that would search that table to find any lists that affect the currently logged in user.

My question is two fold. First, assuming the list is just a-z 0-9 and hyphens and underscores (no spaces in the usernames, but separated with spaces), what would be the best field type to store this as in mysql. Secondly, what would be the best/fastest method for searching for a username across many lists?

Answer 1

You best option would be to store each name in its own row.

Answer 2

If your list contains references to other members (each person has their own list) your best bet is to create the data in an MySQL table of some kind (as MySQL is optimized for this kind of thing), for example you would need 2 simple tables, table one (user table) would have 2 columns. ID (INT or BIGINT), UserName (VARCHAR).

Then your second table (user links) would have 2 entries depending on if linking is done bidirectionally or unidirectionally see bellow.

Bidirectionally: The table would have 2 columns, User1(INT or BIGINT), User2(INT or BIGINT). To retrieve a list, simply query this table for all entries where the user id is in either User1 or User2.

Unidirectionally: The table would have 2 columns, Owner(INT or BIGINT), Person(INT or BIGINT). To retrieve a list for a user simply query this for all entries that are owned by such user.

The difference between the two is, with bidirectionally if i add you to my list, i am automatically on your list. With Unidirectionally that is removed, so you have to add me to your list for me to appear on your list, even if your on my list.

Answer 3

Varchar, indexing and additionally caching.

You're creating a usertable with a username and userid field. Then create a table user_relation where you combine userid's so you have matches. Place indexes on all foreign key relations. That should give a pretty decent performanceboost :)

edit some additional info for setting it up

Table: User UserID - Int (8) - Primary Key, Auto increment Name - Varcahr (30) EmailAddress - Varchar (90) - Unique

Table: UserRelation ID - Int (8) - Primary Key, Auto increment UserID_Source - Int (8), Index (this is the user) UserID_Friend - Int (8), Index (this is a friend / relation from the user)

This way you can easily store relations to a certain user.

To get some results use a query like:

SELECT `User`.*, `User`.`UserID` AS `UserID_Source`
FROM   `User`
WHERE  `User`.`UserID` = `user_relation`.`user_id_source`

You can extend the query by a JOIN to direct fetch the relation matches or use another query for that ;)