CODEWITHSUNDEEP
rdbms
Nistor Alexandru
Nistor Alexandru

Reputation: 5393

Understanding the relations beetween database tables

Hi I am a bit confused regarding relational databases. This is what I understand so far:

Let's say we have two tables users and profile

users : username , password profile: address , telephone , country , city etc.

Oviously a relationship should be created beetween this two tables because each user has an addres ,telephone etc. But how does MySql or Sql-server know witch addres belongs to what user.

I have seen in some tutorials that an aditional field is created in this case users_id for the profile.What I do not understand is if the items in the field are automaticly generated in regards to the user table?Also how can I be sure I am not retrieving the wrong users profile?

Can anyone please explain this concept to me or point me on some tutorials pls?

Upvotes: 0

Views: 239

Answers (2)

Ms01
Ms01

Reputation: 4702

What you will need is a Foreign key in any of the table. Since it's a 1-1 relationship in the example you mention (based on the logic that a user can only have one profile) I would have a field called user_id in the profile table.

TABLE users:
id, username, password

TABLE profile:
id, user_id, presentation

When you create a user use it's inserting-id number when inserting to the profile table.

Example of some bad php code ( do not use this code):

mysql_query('INSERT INTO users (username, password) VALUES ("admin", "admin")');
$uid = mysql_insert_id();
mysql_query('INSERT INTO profile (user_id, presentation) VALUES ( ' . $uid . ', "I love my cats")');

Upvotes: 1

Udan
Udan

Reputation: 5599

Your tables need to look like this

users:
  - id
  - username
  - password


profile:
  - id 
  - user_id // references field id from user as one to one
  - phone
  - etc

A constraint on the relating rule will not allow the record in user table to be deleted (for example) without deleteing the profile record with user.id = profile.user_id

an introduction to relational databases http://www.youtube.com/watch?v=z2kbsG8zsLM

Upvotes: 0

Related Questions