surfer surfer - 6 months ago 16
MySQL Question

Relation between two tables (MySQL)

I have two tables :
users that contain the following columns:

userID int(11) AUTO_INCREMENT PRIMARY KEY
name varchar(50)
address varchar(30)
phone varchar(11)
email varchar(255)
username int(9)
password int(9)
account_permissions enum('a','b','c')
status enum('0','1')


and
private message table that contain the following columns :

id auto_increment primary key
to_user varchar(50)
from_user varchar(50)
subject varchar(400)
message text
date date
del tinyint(1) default value 0


How can I connect the two tables , if user's name will change in the future so I want that it will change automatically in the to_user/from_user columns.

Answer

You should change the private message table to make the to_user an from_user columns be INT(11). They should be foreign keys to the users table userID column.

That way if you ever change the users name in the users table, the joins will remain intact and be unaffected, because it's joined by the unique user identifier.

Also I thikn the username and password fields should be varchar rather than int?

So you might have:

users
userID int(11) AUTO_INCREMENT PRIMARY KEY 
name varchar(50) 
address varchar(30) 
phone varchar(11) 
email varchar(255) 
username varchar(9)
password varchar(9) 
account_permissions enum('a','b','c') status enum('0','1')

messages
id auto_increment primary key 
to_user int(11)
from_user int(11)
subject varchar(400) 
message text 
date date 
del tinyint(1) default value 0

then you could look at the messages like this:

SELECT
    fromuser.username AS from_user,
    touser.username AS to_user,
    messages.subject,
    messages.message,
    messages.date
FROM
    messages
INNER JOIN
    users AS fromuser ON messages.from_user = fromusers.userID
INNER JOIN
    users AS touser ON messages.to_user = tousers.userID

or if you want to add a new message, you just insert the user id's rather than the names.

Comments