[SOLVED] Tables users and table messages, how to link?

[matthewhaworth]

In MySQL: (if that's relevant)

 

I've got my 'users' table.. that looks a little like this :

 

iD, email, password.. blah

 

And I have my messages table (that I haven't created yet):

 

iD, user_sent, user_received, message

 

iD, is obviously the message iD.

user_sent, is the user that sent it's iD

user_recieved, is the user that's receiving it's id

message is the message

 

Now this just screams bad structure to me, what's a more logical approach?

 

 

[hostfreak]

I would create an intermediate table; message_map:

 

Table: users

+----+

| ID |

+----+

 

 

Table: message_map

+-----------+---------------+----+

| User_Sent | User_Recieved | ID |

+-----------+---------------+----+

 

ID -> Primary -> auto_increment

 

 

Table: messages

+---------+--------+----+

| Message | Map_ID | ID |

+---------+--------+----+

 

Map_ID -> ID of record in "message_map"

ID -> Primary -> auto_increment

[Liquid Fire]

You have to ask yourself is it really worth it?  your original method of store the user_id to sent/receive is good and there is no real need to add a "look up table" for something like this.  I generally only use lookup tables when i need to link a id to a set of information or a string or if it is impossible to know how any time one id will be linked to another id.  for instance i might have a this setup:

priority
-id
-title
-color
-status_id

task
-id
priority_id
etc...

This is needed so i am not store data more than i need to however this is over kill(along with what hostfreak said):

priority
-id
-title
-color
-status_id

task
-id
priority_id
etc...

task_priority_link
-id
-priority_id
-task_id

This is overkill because my priority can only have 1 priroity id, this is the same with your message can only have one sender so keep that in the message database.  If you are allowing user to message more than 1 user with a single message then yes you will want to create a separate database to track that information.  here is an example on where you would want a lookup table.  Say you have forum system and users/user group.  This is would i would set it up:

user_group
-is
-title
-status_id

user
-id
-first_name

user_group_link
-id
-user_id
-user_group_id

This way you can have people me is mutliple user groups easier.  Hope this helps you.

[matthewhaworth]

Thanks a lot.