How do I create a private messaging database?
I want to create a PM (personal messaging) system but was struggling with creating a fluid database that links the users of my "user" table to the messages table.
On my PM system, if you had to go to the interface, you will see the sender's avatar, sender's name, and sender's message. However, the database will list the sender and recipient names, the message content along with the timestamp of the message (s) sent. The database will also track if a message has been deleted from the user's mailbox (s).
Here's what I did: Three tables were configured (the "users" table, the "messages" table). The "users" table contains all registered users with the primary id auto_incremented. The "messages" table contains the primary auto_incremented message_id, a string containing user_id, a TIMESTAMP string, the message (s) sent, and a message_status string. Is my setup correct in doing what I want?
The problem I am running into is that the sender's messages are not associated with the intended recipient (in fact, I don't even know where the messages are going).
source to share
When you are trying to write a database schema you need to think about tables like objects (things). The task of a table is to describe one thing or part of a thing. This description is made up of attributes (columns). Each line can only describe one thing or one part of a thing (which means that multiple tables can represent separate parts of one thing). This is called database normalization .
So, in your case, you have 3 main things to worry about.
- User
- Message
- Inbox
If you think about the relationship described between these three things, you can conclude that your schema is basically the basis for a set of answers to a question that you know you will ask later.
For example, if each user must have a mailbox, and each mailbox can have messages, then the schema Inbox
should have a column user_id
that allows you to determine which mailbox belongs to that user. In addition, since a mailbox can contain one or more messages, it must also contain inbox_id
(this will be your auto-increment ID) that will allow you to identify a unique mailbox in the table. Obviously, the schema Message
needs a column message_id
to uniquely identify each message. The schema also needs a column user_id
that identifies which user the post belongs to (i.e. the author of the post).
However, as between Inbox
and Message
there is a ratio of "one to many" and the attitude of "many to many" between User
and Message
then you can not 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 a mailbox and its messages. Name this table Recipient
for now.
The table Recipient
needs to know about the mailbox as well as the message, and which user in the user table is the recipient of that message. This means that you need 3 PKs or primary keys.
-
inbox_id
-
message_id
-
user_id
// this will be the recipient id
Remember that number 3 in this list is the ID of the user to whom the message was sent, not the user who wrote this message (this is already indicated in the table Message
).
Now when you want to know what messages are in a user's inbox, you just query Recipient
and attach it to User
and Message
like this ...
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 = ?
source to share