|When to use a new Field and when to use a new Table|
This has always puzzled me.
Let's say I have a social network where people can add each other as FAVORITES.
How would I store those Favorites, in its own Table where all the favorites from all the users are stored, or in its own Field for that particular user?
If the Favorites are in their own Table, then to show a user who their Favorites are, I have to do a query to get all the rows where the "from" key matches the current user.
If the Favorites are stored in a comma-separated text field in each user's profile, then to show a user who their Favorites are, I have to just pull that field and explode the comma-separated values to display each Favorited user.
Which method is better? Any other, more-efficient methods?
Thanks for your help!
You should definately use the normalized approach of having them in a separate table. That way you can index the columns and gain significant performance gains.
Also, if you ever want to crossreference data from the two users, eg. get a list of my favorites favorites, you would need the additional table for a join-query to work.
If you add all favorites as a comma separated list into a user's table row, then it is not possible to search, on who's favorite list a particular user appears. You may want to tell a user "you are on these people's favorite list" so they can add them as well.
So its definitely better to put it into a separate table. The favorites table would be very small anyway, two INT columns probably, with the two user's ID numbers, and both should be indexed.
Thanks guys, you've been a big help.
I'll go with the table approach then.
A comma-separated list is a form of data table.
So why not simply use a real table, with all its attendant benefits, such as indexing, searching, sorting, inserting, deleting etc?
There's no inherent support for any of the above functions with comma-separated lists in a field in most database systems.