How You Would INSERT ?

[sunny_pro]

Php Masters!

I need to know how you do things the way you do things.
Let us say, I am building a Social Network (SN).
Let us say, my mysql db looks like this in structure:

METHOD 1
Tbl: users;
Columns: id, username, email, friends.

Example:

id|username|email|friends
0|uniqueideaman|uniqueideaman@**.com|user1,user500 ,user697

Do you see, how I have crunched-up 3 usernames into a single cell ?
Q1. Is this how you would do it to list my friends' usernames ?

---------------

METHOD 2:

I was told I should do it like this:

Anyway, I was told to check these out:
https://en.wikipedia.org/wiki/Fourth_normal_form


Did not bother with the vid as I'm on low bandwidth.

Also, googled:
https://www.google.com/search?q=what...hrome&ie=UTF-8



You php pro, do you think I should have a friends list where every member link is recorded in a three column table, the first being an auto_increment ID to keep all records sequential and unique, the 2nd ID is the User ID (UID) and the Friend ID is the FID ?

Tbl: Friends
ID is auto incremented.
UID is User's ID.
FID is Friend's ID.

Columns
ID | UID | FID
1 | 3 | 24
2 | 3 | 399
3 | 55 | 24
4 | 598 | 3
5 | 598 | 55
6 | 6000 | 598
7 | 3 | 598
8 | 24 | 55
9 | 55 | 598

So, you think apart from having the "users" tbl that lists the users during registration, I should have another tbl "friends" ?
And you want me to populate the "freinds" tbl like shown above. Right ? Yes or no ?
If "yes", then how would you db query to pull-up a user's friends ? Let's say you want to pull-up UID 5's friends.
This is my try using PREP STMNT:

$stmt = mysqli_prepare($conn, "SELECT FID FROM users WHERE UID = ? ");

In the above example you can see that ID's 1 & 2 have two entries for UID 3 who is linked to friend's FID 24 & 399 and is also friends with UID 598.
It appears that I am having multiple links here. And therefore, I did not originally want to do it this way to save the db getting populated to much (too many entries) to save it from getting bogged down from doing too many tasks.

Q2. Would not it be less resource using and less querying (hence less traffic to the db) if I do it the way I demonstrated in my original post ?

 

[Rob Whisonant]

Your method 1 is called a flat file system.

Your method 2 is called a relational database system.

Method 2 is much better to use. Here are a few reasons.

1. You have no way of knowing how many friends a person will have, so you have to make method 1s friend field huge for everyone. So your database gets bloated.

2. If you want to use the relationship of friends in multiple tables it is a lot easier.

3. When you query a database, as long as you are only querying what you need, it is extremely fast.

4. mySQL is designed to be used as a relational database and not an old fashioned flat file.

5. Always let the database query language handle what it is designed to do. Query results and build relationships. That's one of its jobs. Don't try to do it in PHP. AKA use the proper tool for the proper job.

6. When you need to delete a person that is friends with a lot of people, it's much easier and faster to delete using method 2.

 

[sunny_pro]

sunny_pro

Thank you Mod!
No, method 1 I can do in mysql too. yes, others have sugegsted I stick to method 2 and frowning on method 1. I've now got the message from all pro php programmers regarding this issue.

Thank you!