Database Design Help (For PHP)

[isotope328]

In reading through the topics in the book I am finding the concept of normalization to be a little confusing, particularly when it comes to how it should be applied to my needs. I need to create a database with a variety of user information. The table might have uniqueID, name, address, favorite color, ect. And finally a columns called other users. The other users column would be, as the same implies the Unique ID of all the other users in that individuals “network”. Lets say me, John Doe has ten other users in my “network”. I could store a list of all those users Unique IDs in one of the columns but this breaks the rules of proper database design. My question is, what is the best way to do this. I know the example is a little confusing but I don’t know how else to phrase it. Basically each user needs to be linked to a number of other users from the same database. Each of those users is in turn linked back to that user and perhaps a different set of users much like a node. I included a diagram to help.


Kevin
Joe --------|
| Ron
| | |
| ---Phil---Jen


Joes Network; Kevin, Ron, Phil
Kevin’s Network; Ron, Joe
Ron’s Network; Phil, Jen, Kevin, Joe
Phil’s Network; Jen, Joe, Ron
Jen’s Network; Ron, Phil

 

[sleipnir214]

Since you can have any number of people in another person's network, I would record that information in a relating table.

So I'd have two tables. One records the actual attributes of a user: ID number, name, address, etc.

The other table records the relationships between any two users. It'll have two columns: one to record the userid of a user, another to record the userid of another user. A single row describes that the user in the first column as the user in the second column in his network.

Thus the two tables' contents:

The users table:

ID    Name
 1    Joe
 2    Kevin
 3    Ron
 4    Phil
 5    Jen

The networks table:

ID    Network_member
 1    2
 1    3
 1    4
 2    3
 2    1
 3    4
 3    5
 3    2
 3    1
 4    5
 4    1
 4    3
 5    3
 5    4

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[isotope328]

Splendid, big help, I still have a couple questions though. Lets say there is 1000 distinct individuals in my database some people have 1000 people in their network some have 1 for an average of 500. So, if 1000 people have 500 people in their network that’s 500,000 rows in the second table. Won’t this table become very large very quickly. Also, in the example given before, if A is in B’s network B is in A’s network. In the table however each relationship repeats once. I added an @ symbol in front of one combo to illustrate. Just a thought, I’m reading a book on MySql and it say if a set of values in the database repeats . . .that means there is a better way to represent the data I’m not sure if this example fits that scenario . . . thanks for your help


The users table:
ID Name
1 Joe
2 Kevin
3 Ron
4 Phil
5 Jen


The networks table:
ID Network_member
1 2
1 3
@1 4
2 3
2 1
3 4
3 5
3 2
3 1
4 5
@4 1
4 3
5 3
5 4

 

[sleipnir214]

Your database schema must take into account how you're going to use the data you're storing. There are times when it's much more efficient to violate normal forms in a database, particularly if it enhances query performance.

I don't know what your definition of a "network" is, but you might be able to abstract the concept.

Use the original "users" table and drop original "networks" table. Create a new "networks" table that defines a network: name of the network, focus of the network, who's the moderator. Create a "network_to_users" table that has two columns: the ID of a network and the ID of a user.

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!