Linking databases (PHP / MySQL)

gnolan

For all intents & purposes i'm attempting to create a social network site - moreso for the experience than anything else. This is my first time approaching the idea of relational databases.

I have a "user" table and a "profile" table. The user table contains the user's basic details; name, email, username etc. The primary key is an auto-incrementing userID.

The profile table contains other details; like FB profile, Twitter profile, user image etc. The primary key here (rightly or wrongly) is the user's email address. My problem comes with how to link these databases.

Upon registration, the user's firstname, surname & password are required. If registration is successful should I be adding the user's email to the profile table, or is there a more dynamic way of doing this.

To be honest, i think i'm probably missing some fundamental knowledge concerning the reasoning behind relational databases.

jmcc

gnolan wrote: »

For all intents & purposes i'm attempting to create a social network site - moreso for the experience than anything else. This is my first time approaching the idea of relational databases.

I have a "user" table and a "profile" table. The user table contains the user's basic details; name, email, username etc. The primary key is an auto-incrementing userID.

The profile table contains other details; like FB profile, Twitter profile, user image etc. The primary key here (rightly or wrongly) is the user's email address. My problem comes with how to link these databases.

The tables should be (probably are) part of the same database. The e-mail address shouldn't be used as a primary key. You could have an e-mail address field in the profile table as well as the user table. That gives you a simple connection between the two tables. (Probably very iffy in a real situation but fine for simple experimentation.)

There should be some online tutorials that you could read that would explain the basics of relational databases. (Perhaps others could provide a few links.)

Regards...jmcc

gnolan

jmcc wrote: »

The tables should be (probably are) part of the same database. The e-mail address shouldn't be used as a primary key. You could have an e-mail address field in the profile table as well as the user table. That gives you a simple connection between the two tables. (Probably very iffy in a real situation but fine for simple experimentation.)

There should be some online tutorials that you could read that would explain the basics of relational databases. (Perhaps others could provide a few links.)

Regards...jmcc

Yes, both tables are in the same database and both tables have an email field. For the user i was using the autoincrementing userID as the primary key. What do you think that using the email as a primary key in the 'profile' table wouldn't be suitable - given that any given email address will only occur once i.e. not allowing multiple registrations per email?

If not email what would you suggest?

Paddy GT

Why not create a profileID (auto increment) field for the primary field in the profile table and add an extra field called userID which has the corresponding userID from the users table.

That way you have created a link between the 2 !??

chakotha

It sounds like most of the items in the profile table could be in the one users table as each user will only have one of each item eg. FB profile, Twitter profile, user image etc.

If you do keep the profile table separate then yes I'd use a profileid as primary key in the profile table and a userid to make the link.

If a user is going to have multiple items associated with him/her eg. photo albums you do need another table eg. a photo_album table with primary key albumid and a userid field linking each album to a user.

Tom Dunne

Like most things in IT, there are many ways you could approach this.

The first thing I would say to you is to do your best not to have duplicate data. It can be fine in a tiny db like this, but once you add a few more tables, you may have to start again.

Next, null is not data. So, for example, if your user has a Facebook profile, but not a Twitter profile, you don't want to store the latter as null, or blank data - just don't store it.

Where I am going with this is, you could have another table with a list of profile types, e.g. Facebook, Twitter, etc. The beauty of this approach is that you add new profile types in one place only, you don't have to modify the profile table as you have defined it. You record user_id 7, say, has profile_id 1, 2 , 6. The fact that profile ids 3,4 & 5 are not stored imply user_id 7 does not have such a profile - no need to store this, though.

Using email addresses as a primary key is a big no-no in this case. What happens if the user changes his email address? The primary key changes and that could potentially mess things up.

First step - make a list of everything you want to store and group them accordingly. These could be your tables.

off.the.walls

<?php
// Created BY Adam Khoury @ www.flashbuilding.com - 6/19/2008
/*
1: "die()" will exit the script and show an error statement if something goes wrong with the "connect" or "select" functions.
2: A "mysql_connect()" error usually means your username/password are wrong
3: A "mysql_select_db()" error usually means the database does not exist.
*/
// Place db host name. Sometimes "localhost" but
// sometimes looks like this: >> ???mysql??.someserver.net
$db_host = "place_your_db_host";
// Place the username for the MySQL database here
$db_username = "place_your_username";
// Place the password for the MySQL database here
$db_pass = "place_your_db_password";
// Place the name for the MySQL database here
$db_name = "place_your_db_name";

// Run the actual connection here
mysql_connect("$db_host","$db_username","$db_pass") or die ("could not connect to mysql");
mysql_select_db("$db_name") or die ("no database");
?> thats the code i've used a few times

arleitiss

Personally when I work with databases, no matter what it is, users, logins, emails, statistics whatever I always have id with auto increment and set as primary key as first field. So I can always address specific data by their ID and it's very fast too

gnolan

Thanks everyone for the replies, it's very helpful.

The first thing I would say to you is to do your best not to have duplicate data. It can be fine in a tiny db like this, but once you add a few more tables, you may have to start again.

When you say not to have duplicate data, do you mean on large scale? What i mean is, it is fine to have one duplicate field, for example, in 4 tables - with that field being the one used to link the tables?

.....you could have another table with a list of profile types, e.g. Facebook, Twitter, etc. The beauty of this approach is that you add new profile types in one place only, you don't have to modify the profile table as you have defined it. You record user_id 7, say, has profile_id 1, 2 , 6. The fact that profile ids 3,4 & 5 are not stored imply user_id 7 does not have such a profile - no need to store this, though.

I'm not sure i really grasp this to be honest. Say i have a socialNetworks table with the userID field being the primary key. Say i allow 7 different types of social networks to be recognized. Do i have 7 different fields (like i do now)? I'm fairly sure you're saying this is exactly what not to do but i don't really understand your approach. In each of my social network fields (eg. facebookProfile) i'm storing the web address of that user's specific social network profile.

<?php
// Created BY Adam Khoury @ www.flashbuilding.com - 6/19/2008
/*
1: "die()" will exit the script and show an error statement if something goes
........
........
........
mysql_select_db("$db_name") or die ("no database");
?> thats the code i've used a few times

From what i've been reading, it's best to start moving away from mysql_ functions and begin using mysqli.

Tom Dunne

gnolan wrote: »

When you say not to have duplicate data, do you mean on large scale? What i mean is, it is fine to have one duplicate field, for example, in 4 tables - with that field being the one used to link the tables?

I am referring to, say, having the word "Facebook" stored every single time a user has a Facebook profile. So if you have 1,000 users, 978 of which have a Facebook profile, that is the word Facebook stored 978 times. If, for some reason, Facebook decides to change its name to, say, "Social You", you would have to change the word "Facebook" in 978 rows in the database. However, if you had it stored once, in one row, in one table, you would only have to change it in one place - much more efficient.

What you are describing above is in fact fine.

gnolan wrote: »

I'm not sure i really grasp this to be honest. Say i have a socialNetworks table with the userID field being the primary key. Say i allow 7 different types of social networks to be recognized. Do i have 7 different fields (like i do now)? I'm fairly sure you're saying this is exactly what not to do but i don't really understand your approach. In each of my social network fields (eg. facebookProfile) i'm storing the web address of that user's specific social network profile.

No. Say you have three tables: user_details, profiles and user_profiles.

user_details would have user_id, username, password, email, etc
Profiles would have profile_id, profile_name, profile_description

user_profiles would have user_id, profile_id

So, you use the user_profiles table to record what profiles a user has. There would be duplicate data in this table, but that is by necessity, it is not a problem. There would be no duplicate data in either user_details or profiles.

bobbytables

Hey OP just be careful in future to be clear on the difference between Databases & Tables. You are seeking to establish a relationship beween tables.

In your case I assume there is a 1:1 relationship between a user & profile. If that is so then perhaps consider creating a "user" foreign key in the profile table. No need to be any more complex than that to model a relationship between those two.

amen

In your case I assume there is a 1:1 relationship between a user & profile

I know what you are saying but strictly speaking there is a 1:many relationship between the user and userprofile table.

Baz_

bobbytables wrote: »

Hey OP just be careful in future to be clear on the difference between Databases & Tables.

I know and agree what the op said was confusing, but I don't think it was done on purpose. He was as clear as he knew to be, he's just getting his head around and discussing a fairly difficult topic, I think a break is in order...

(Apologies to op if you're a girl, no offense was intended in the writing of this post)

gnolan

Tom Dunne wrote: »

....you have three tables: user_details, profiles and user_profiles.

user_details would have user_id, username, password, email, etc
Profiles would have profile_id, profile_name, profile_description

user_profiles would have user_id, profile_id

So, you use the user_profiles table to record what profiles a user has. There would be duplicate data in this table, but that is by necessity, it is not a problem. There would be no duplicate data in either user_details or profiles.

So you're saying that if a user with userID 28 has a facebook, twitter and linkedin profile I could put something like "2, 4, 5" into their profileID field like below?

userID

---

profileID
----28

---

2,4,5

I can see how this would work if i'm simply checking if they have one of the profiles, but i want to store the web address of each profile they have - hence me having something like below:

amen

no what he means is three tables

[PHP]
User Table
UserID
UserName
Password
etc...


SocialProfiles Table
ProfileID
SocialNetworkName
SocialNetworkURL

UserProfiles Table
UserID
ProfileID

[/PHP]

So if we support FaceBook, Twitter, and LinkedIn then the data in the SocialProfiles table would be

[PHP]
ProfileID SocialNetworkName SocialNetworkURL
1 FaceBook www.facebook.com
2 Twitter www.twitter.com
3 LinkedIn www.Linkedin.com
[/PHP]

So is we have a user, TestUser with a UserId of 28 and who uses FaceBook and LinkedIn then in the UserProfiles table we would have two entries for user 28.

[PHP]
UserID ProfileID
28 1
28 3
[/PHP]

Now if UserID 28 obtained a Twitter account they would now have three entries.

[PHP]
UserID ProfileID
28 1
28 3
28 2
[/PHP]

The advantage of this model is that it is easy to add a new social network. Say for instance you now wanted to support Google+ then you add an entry to SocialProfiles Table

[PHP]
ProfileID SocialNetworkName SocialNetworkURL
1 FaceBook www.facebook.com
2 Twitter www.twitter.com
3 LinkedIn www.Linkedin.com
4 Google+ www.Google.com
[/PHP]

Now if UserID 28 obtained a Google+ account they would now have four entries.

[PHP]
UserID ProfileID
28 1
28 3
28 2
28 4
[/PHP]

If you used your model then everytime you added a new social network you would have to modify your table. You are also using storage to record null values.

You need additional columns on the UserProfiles table such as ProfileDateAdded etc.

I would suggest you do some reading on Database Structure/Design and Normalisation.

What DB are you using ?

gnolan

amen wrote: »

If you used your model then everytime you added a new social network you would have to modify your table. You are also using storage to record null values.

You need additional columns on the UserProfiles table such as ProfileDateAdded etc.

I would suggest you do some reading on Database Structure/Design and Normalisation.

What DB are you using ?

Thanks, i think i get it now.

Can you recommend some reading or online resources?

As for what DB i'm using - i'm not sure? I'm using php 5.3.6 on my localhost.

Baz_

gnolan wrote: »

As for what DB i'm using - i'm not sure? I'm using php 5.3.6 on my localhost.

Are you making calls to MySQL functions, in fact I think you mentioned earlier something about MySQLi functions, so MySQL would be your database software.

gnolan

Baz_ wrote: »

Are you making calls to MySQL functions, in fact I think you mentioned earlier something about MySQLi functions, so MySQL would be your database software.

Yeah, that's right - i'm using MySQL.

Baz_

As for a book. If you go to bookboon.com they have a book or two on relation database introduction type topics. You just go to their site give them your email address and sign up for a newsletter and you can then download as many of there books as you want. I'm not sure how good it is at covering the topic and its heavily advertising sponsored publication, but so what, its free. Give it a go.

The MySQL documentation itself is quite good also and well worth downloading. However, I don't think its introductory iirc.

Tom Dunne

I've often found the SAMs Teach yourself X in 24hours series quite good. I'm pretty sure there is one on PHP/MySql.

Baz_

the web book by robert schifreen used to be a free downloadable resource. It still seems to be accessible to read here. But if you want to be able to download it it looks like you have to go through amazon kindle. It's only a nominal $3.68, but you have to have a kindle or the kindle app on one of your devices. But at least you can have a read at the link above and decide if it's worth buying.

gnolan

Thanks for the suggestions. I'll probably be back before long looking for more advice re normalization and the like!