homepage Welcome to WebmasterWorld Guest from 54.166.8.138
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Pubcon Platinum Sponsor 2014
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderators: physics

Databases Forum

    
When to use a new Field and when to use a new Table
NeedExpertHelp




msg:4250825
 11:35 pm on Jan 8, 2011 (gmt 0)

Hi,

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!

 

DWarp9




msg:4251080
 10:31 pm on Jan 9, 2011 (gmt 0)

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.

-Peter

yaix2




msg:4251123
 2:54 am on Jan 10, 2011 (gmt 0)

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.

NeedExpertHelp




msg:4251134
 3:46 am on Jan 10, 2011 (gmt 0)

Thanks guys, you've been a big help.

I'll go with the table approach then.

drongo




msg:4251776
 12:30 pm on Jan 11, 2011 (gmt 0)

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.

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved