People experienced with php + database work - advice needed

[xpiamchris]

Hey guys

I am planning on creating a database that's primarily messages exchanged b/w one person and another. what's the best way to set this up in the long run.

What I have:
3 separate tables:
A table = list of group A people
B table = list of group B people
C table = messages b/w A and B

C table
messageid | aperson | bperson | time | message


i'm guessing this isn't how facebook does it?
And this seems VERY basic... what am i missing or how can i improve this?

[NogDog]

Any particular reason the people need to be split between 2 tables? If the data for each type of person is the same, I would just have one table to which you could add a column to specify if they are type A or B.

And there's nothing wrong with "basic" if it gets the job done. In fact, "basic" is often better than "complex".

[Mindzai]

Why two tables for users? I'd have the following:

users:
id, name, etc

messages
id, message, etc

users_messages
user_id, message_id

This will give you a good structure to use within your code, plus the flexibility of conversations not limited to two people. If this is definitely never going to be a requirement you could drop the join table and add the user's ids as foreign keys in the messages table.

[xpiamchris]

mm i guess two tables isn't really needed.. its just table a users will never interact with each other and table b users will never interact with each other. there will only be cross interaction. does that make a difference?

i dont know.. maybe im overthinking it..
besides the users involved, the message, and the time... am i missing anything else that needs to be documented?

thanks guys for your quick responses!

[Mindzai]

mm i guess two tables isn't really needed.. its just table a users will never interact with each other and table b users will never interact with each other. there will only be cross interaction. does that make a difference?

No, a user is a user regardless of how they may interact within your system. That kind of logic is best handled by the PHP side. The database is really there just as a repository for data (although relationships between different 'entities' like users and messages obviously needs to be considered at the database level).