php - How to design a personal messaging database? -

i wish make pm (personal messaging) system have been struggling in setting fluid database links users of 'user' table messages table.

in pm system if go interface, see avatar of sender, name of sender, , message of sender. database have names of sender , recipient, content of message along timestamp message(s) sent. database keep track if message has been removed user's(s') inbox.

here i've done: 3 tables have been setup ('users' table, 'messages' table). 'users' table contains registered users primary auto_incremented id. 'messages' table contains primary auto_incremented message_id, row contains user_id, row timestamp message(s) was(were) sent, , row message_content. setup correct in fulfilling want?

the issue having messages of sender not linking intended recipient (in fact, don't know messages going).

when you're trying write database schema have think of tables entities (things). table's job describe single thing or part of thing. description made of attributes (columns). each row can describe 1 thing or 1 part of thing (meaning multiple tables can represent individual parts of 1 thing). called database normalization.

so in case have 3 primary things concerned with.

1. a user
2. a message
3. an inbox

if think relationships described between these 3 things can conclude schema framework set of answers question know ask later.

for example, if every user must have inbox , every inbox may have messages inbox schema needs have user_id column allows identify inbox belongs user. additionally, since inbox may have 1 or more messages must contain inbox_id (this auto increment id) allow identify unique inbox in table. message schema needs message_id column uniquely identify each message. schema needs user_id column identifies user message belongs (i.e. author of message).

however, since there one-to-many relationship between inbox , message , many-to-many relationship between user , message can't describe relationship between them in same table without creating logical inconsistencies. called 3nf or third normal form.

so instead create fourth table describes relationship between inbox , messages. let's call recipient table now.

the recipient table needs know inbox message , user in user table recipient of message. means need 3 pks or primary keys here.

1. inbox_id
2. message_id
3. user_id // id of recipient

remember, number 3 on list id of user message sent to, not user wrote message (that identified message table).

now when want know messages in given user's inbox query recipient , join on user , message so...

select mesage.user_id sender, message.contents, recipient.user_id recipient recipient, inbox join message on recipient.message_id = message.id inbox.user_id = ?