I wish to make a PM (personal messaging) system but have been struggling in setting up a fluid database that links the users of my 'user' table with the messages table.
In my PM system if you were to go to the interface, you will see the avatar of the sender, the name of the sender, and the message of the sender. The database however will have the names of the sender and recipient, the content of the message along with the timestamp the message(s) was sent. The database will also keep track if a message has been removed from a user's(s') inbox.
Here is what I've done: Three tables have been setup ('users' table, 'messages' table). The 'users' table contains all registered users with a primary auto_incremented id. The 'messages' table contains a primary auto_incremented message_id, a row that contains user_id, a row with the TIMESTAMP the message(s) was(were) sent, and a row with message_content. Is my setup correct in fulfilling what I want?
The issue I am having is that the messages of the sender are not linking with the intended recipient (in fact, I don't even know where the messages are even going).
When you're trying to write a database schema you have to think of tables like you would entities (things). The table's job is to describe a single thing or some part of a thing. That description is made up of attributes (columns). Each row can only describe one thing or one part of a thing (meaning multiple tables can represent individual parts of one thing). This is called database normalization.
So in your case you have 3 primary things you are concerned with.
If you think about the relationships described between these three things you can conclude that your schema is basically just the framework for a set of answers to question you know you will ask later.
For example, if every user must have an inbox and every inbox may have messages then the Inbox schema needs to have a user_id column that allows you to identify which inbox belongs to which user. Additionally, since an inbox may have one or more messages then it must also contain an inbox_id (this would be your auto increment id) which would allow you to identify a unique inbox in the table. Obviously the Message schema also needs a message_id column to uniquely identify each message. The schema also needs a user_id column that identifies which user the message belongs to (i.e. the author of the message).
However, since there is a one-to-many relationship between Inbox and Message and a many-to-many relationship between User and Message then you can't describe the relationship between them in the same table easily without creating logical inconsistencies. This is called 3NF or Third Normal Form.
So instead you create a fourth table that simply describes the relationship between an inbox and its messages. Let's call this the Recipient table for now.
The Recipient table needs to know about the inbox as well as the message and which user in the user table is the recipient of this message. That means you need 3 PKs or Primary Keys here.
inbox_idmessage_iduser_id // this will be the id of the recipientRemember, number 3 on that list is the id of the user that the message was sent to, not the user that wrote the message (that is already identified by the Message table).
Now when you want to know which messages are in a given user's inbox you simply query the Recipient and join it on User and Message like so...
SELECT mesage.user_id AS Sender, message.contents, recipient.user_id AS Recipient
FROM recipient, inbox
JOIN message ON recipient.message_id = message.id
WHERE inbox.user_id = ?