'friends list'

[ronjon]

hello,

heres the situation...

im making an online community with php and mysql...one very similar to www.friendster.com and www.myspace.com. if you are not familiar with these sites...they are online communities that allow you to build a profile and build a list of 'friends'...
im at the point where im trying to figure out a way to create a table for the 'friends list'

i figured out that there are several ways to do this...

a. i can store the list of friends that a user may have within the users' profile table... i.e.

username: 'user1'
email: '[e-mail]'
friendslist: '[e-mail]'
gender: 'm'

username, email, friendslist and gender being fields in a 'members' table

im shyed away by this because for some reason it seems really fumbly and prone to error.

or (and this one seems to me the easiest...but also seems ridiculously unefficient...and im not sure if myssql would be able to handle so many hits on this table.... everytime a user browses a user's page, where it shows their friendslist...it would hit this table and scan the whole thing for every entry for a user and a friend)

b.
have a huge table that has two fields...
"field1" being member and field 2 being "friend"

|member| |friend|
user1.....user2
user1.....user3
user2.....user1
user3.....user1
user7.....user1
user1.....user14
user2.....user200
user32.....user54
user1.....user7
...you get the picture

this seems really ridiculous to me because of the number of hits this table would endure...and the redundancy. it attracts me though because of its simplicity....

c. i could create a table for each member on the fly with a single field that just lists their friends emails... which also seems pretty simple...but it also seems to me like im opening a can of worms allowing my database to grow a huge number of tables indefinitely depending on how many users i attract (a new table for each new member)

i understand that some of thse concerns that i have my be really off and shouldnt be a concern at all....or that something that i may not show too much concern about is an absolute programming no no...

and thats why i turn to you folks... im a newbie...a real newbie...and am not too familiar with creating schemas like this and dont know the pros and cons too well of certain database designs over others...so i seek the help of you pros to guide me in the right direction...

if there is a way thats the best or is an industry standard for creating an online community that i havent listed above... please... by all means let me know so that i can go that direction. i dont care too much as to which direction i head so long as its the best direction.

again... thank you all very much in advance... you guys always help me out and you all have my sincere thanks.

-ron

[yelvington]

(b).

http://www.utexas.edu/its/windows/database/datamodeling/rm/rm4.html

[ronjon]

i understand the whole concept and how it works... i know it will work. what i am concerned about is the efficiency of the 'b' schema.

heres the situation:
im trying to build a online community. each member will have his or her own page and these pages are viewable by other members. these member pages will list a number of members they have 'added as friends' (currently... im thinking a preview of 10...with an option to click on a link that will take them to a different page that can show all friends of that particular member they are currently viewing). here is my concern... the 'b' option that i listed above will have one huge table of members and a friend. this will result in field one listing a certain member and 'a friend'....so if 'user14' has 14 friends... user14 will be listed in field one 14 times...consequently...user14 will also be listed in field two (the 'friend' field) 14 times...

if any member has 145 friends...they would be listed in field one 145 times...and in field two 145 times.

let me also remind you that there is just one 'friends' table for all members, holding nothing but a member on field 1 and a friend of that member on field 2.... given that each member profile page has a list of friends shown lets say 10 friends for now; each time any member hits his or somebody else's profile page.... the huge friends table would have to be scanned for a each member's list of friends....

i dont know if you all are familiar with friendster.com or myspace.com...but theyre quite popular... friendster within its first year have gathered 1.7 million members each member averaging about 40 friends. now... ill not be naive and say that ill have that many members.... most especially since my site is going to be just for dallas... but... lets say i acquire 2000 members... which is completely possible... id have 2000 members with 40 friends.... thats a table thats going to have 80,000 entries... a table that will have to be hit a lot ...since it holds data that every page that my website will have; the friends list. lets say ... 5 percent of those 2000 members hit the website between 5pm to 6pm. each staying for an hour or so to browse member pages and look at pictures and so forth.... and lets say they hit ten different member pages within that hour (which is very modest...i browse myspace all the time and i can probably hit 30 pages in an hour)....

that 80,000 entry database would be hit 1000 times by 100 different members, retrieving 10,000 entries in an hour. (again thats a very modest estimate...given that i might be lucky enough to gather 2000 members...).... that number to me seems really high for a database like mysql to handle.... AND THATS JUST THE 'friendslist' table... there would be 6 other tables that would be hit about 60% of that number of hits.....

oh by the way ill be running mysql, php and iis on an athlon 2.5 barton machine with 512mem.... 7200 rpm ata133 drive.

as ive said before... im a newbie...and im not sure of the capabilities of mysql and such a machine....its just a gut feeling that i have about those numbers that make me feel like all this free software im using ... mysql and php and my cheap computer would not be able to handle all of that load... making me ever so curious if there is a more efficient way of doing this....

i guess this gut feeling comes from my idea that databases have to 'scan' entries one by one to check to see if an entry matches the sql query.... or atleast thats what they make us conceptually think in school... if this is not so... let me know... maybe mysql has a better way of retrieving and scanning data from a table than going one record after the other.... and if youre feeling kind and wanting to enlightedn an eager newbie, maybe you can give me an explanation of how this whole process works...the actual process that a database 'scans' for records...

if my gut feeling is wrong...and mysql and my machine would handle this just fine by your experience...please tell me....
if not... please let me know of a more efficient approach if you know of one. i really wanna create this thing right from the get go so i would have less upkeep or upgrade as possible.

i know im asking a lot... and maybe im asking really elementary stuff from you professionals... but... i just dont know how else to figure this out without spending days of research...

thanks again to all of you in advance..
-ron

[yelvington]

I'm a strategist and journalist, not a coder, so take the following for what it's worth. I'd be happy to see a cogent counterargument from a knowledgeable source.

• Any relational database is designed to support table joins. What you're asking MySQL to do with structure B is not out of the ordinary.

• Premature optimization is the root of all evil. Design for function and maintainability, and optimize after you have discovered the true bottlenecks in real-world testing.

• In my experience, the most egregious database performance problems have been the result of operator error -- a dropped index, for example. On the other hand, a competent database administrator can work wonders with performance turning.

I don't have accounts on friendster or myspace, but I do have accounts on Orkut and LinkedIn. Orkut performance is abysmal, so I understand the potential of a problem, but I seriously doubt that sound table design is the cause.

Caching -- both data caching and PHP compiled-image caching -- is a powerful tool for accelerating performance.

Often pages are constructed through dynamic processes when they could/should be "baked" into static HTML.

[juhhuh]

hi ron,

i came across your posting from a couple years ago, and am interested in how you decided to implement your database schema. i have this similar problem with a site i am building and was thinking along the same lines as you. however, i'm thinking option A might be more efficient to parse one field the database and run the sql query against that. any feedback would be greatly appreciated.

thanks, john.