Jump to content

How You Would INSERT ?


saversites

Recommended Posts

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
https://www.youtube.com/watch?v=UrYLYV7WSHM

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

And read:
https://www.tutorialcup.com/dbms/fourth-normal-form.htm


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 ?


 

Edited by saversites
Link to comment
Share on other sites

  • 4 weeks later...
On 1/8/2018 at 2:01 PM, saversites said:

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 ?

No. To track a users friends, that would probably be best handled in it's own table. So I would have perhaps a 'friends' table that would have perhaps three fields:

  1. user id
  2. friend user id
  3. date of frienship

... Something like that. 

So for each friend a user has, a new record (row) would be created. And then, you would apply an index to the field 'user id' ... for speed.

Stef

  • Like 1
Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
×
×
  • Create New...