Topic: [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:

Code: [Select]

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):

Code: [Select]

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:

Code: [Select]

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.