homepage Welcome to WebmasterWorld Guest from 54.145.238.55
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

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

5+ Year Member



 
Msg#: 4250823 posted 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

5+ Year Member



 
Msg#: 4250823 posted 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

5+ Year Member



 
Msg#: 4250823 posted 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

5+ Year Member



 
Msg#: 4250823 posted 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

5+ Year Member



 
Msg#: 4250823 posted 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