Designing Complicated Table Structures

[rudy507]

Hey guys,

I am working on a large addition to my 2-year old website right now. Basically, it is going to have a "myspace" or "facebook" feel to it - there's going to be profile info, friends, personal messages, etc...

 

I'm writing the table structure down on paper right now, as I do have a bit of experience designing databases...

 

At any rate, I am trying to figure out the best way to design a table or field that enable users to be friends with each other.

 

There are two options I'm considering:

1) 1 field, inside of TABLE "user" with comma separated values of other user IDs

- is this even possible? If so, I don't know how to separate the IDs in the query to SELECT them and display them in one's profile page

 

2) A totally separate TABLE with two main fields: ID1 and ID2, with basically the IDs of two people who become friends being inserted into a row.

- This just seems way too ridiculous. My initial thoughts with this design is a LOT bigger than necessary in size (when/if the website grows to hundreds or thousands of people)

- It also seems like that with this structure, the SELECT and output of "my friends" and "his friends" would just take forever... sorting through all those rows... woah. Seems pretty inefficient to me.

 

Ok, any ideas? Any suggestions? I'm all ears.

 

Thanks,

David

 

 

[fenway]

1) Bad idea...

2) 100K records isn't big.

[rudy507]

So really, idea 2 isn't a bad idea?

 

To clarify my thoughts on idea 2, just in case, is to do the following:

 

Every time "A" gains a friend, a new row is inserted into the table "friends" like this:

 

A, B

A, C

A, D

A, E

 

Ok, so F decides to join, and he becomes friends with everyone except C:

F, A

F, B

F, D

F, E

 

So now I have 8 different relationships. A is logged in. The php will search through this table "friends" and will find out every person that A has a "relationship" with, regardless of if A is in the 1st column or 2nd column.

 

After searching, we find that A has relationship with B, C, D, E and F. We now display these as A's Friends.

[PHP Monkeh]

That's one way you can do it.  Or you could store all their friends in a single field, then put a seperator in (lets say |).  So the field would look like:

 

F's Friends: A|B|C|D|E

 

Then you can explode by | and find out each of the values.  This way you could include it in to their user table, rather than making an entirely new one.

[fenway]

That's one way you can do it.  Or you could store all their friends in a single field, then put a seperator in (lets say |).  So the field would look like:

 

F's Friends: A|B|C|D|E

 

Then you can explode by | and find out each of the values.  This way you could include it in to their user table, rather than making an entirely new one.

NO!

 

This is a pain to edit, and even more of a pain to search.  The only time such a list should ever be used if when you never need to modify individual values.

[GingerRobot]

Fenway, would you even use such a list if you thought you would not need to modify individual values? As you said, its still a pain to search.

 

Just curious - i wasn't sure there was ever a situation where it would be a good idea.

[rudy507]

Alright, then I guess idea 2 is the only practical way to do it... just wanted to find out opinions about practicality if/when the database grows to thousands or even (not probable, but possible) millions of users.

 

Thanks for the input guys.

[PHP Monkeh]

fenway:

 

I would never do that, but rudy was asking about possibilities! 

[fenway]

Fenway, would you even use such a list if you thought you would not need to modify individual values? As you said, its still a pain to search.

 

Just curious - i wasn't sure there was ever a situation where it would be a good idea.

I use this all the time whenever I'm storing "system" information that is never edited except by hand, and quite infrequently.

 

Name/value pairs are quite easily stored in this fashion, and as long as you read them back all at once, it's fine.

 

Just don't expect the DB to be able to handle this in any meaningful way.  Yes, FIND_IN_SET() can be hijacked to deal with very simple boundary cases, but it's bad design.