Modeling Private Messages

[doubledee]

I have successfully created User Accounts and Account Management features on my website, so people can add Comments to my Articles.

 

Now I would like to add the ability for (registered) Users to send Private Messages back and forth similar to how you can here.

 

On one hand this doesn't seem like it should be a difficult topic.

 

But on the other hand, after A LOT of research, I haven't been able to find any "standards" or "best practices" for creating a Private Message Systems... 

 

I could really use some help design things from a MySQL standpoint...

 

Things can be made even more complicated if you allow one User to PM *multiple* Users.  More so, if you create the concept of *Conversations* (think "Threads") then that makes things trickier too.  (How useful do you think these would be??)

 

For now, I would be very happy if User A could send User B a PM and visa-versa.

 

Here are some ideas in my head of how to tackle this...

 

Relationships:

- One SENDER can send zero to many MESSAGES

 

- One RECIPIENT can receive zero to many MESSAGES

 

- A SENDER can be a RECIPIENT

 

- A RECIPIENT can be a SENDER

 

 

Private Message:

- id

- sender_id

- recipient_id

- subject

- body

- sent_on

- new_message_status (recipient)

- recipient_read_status (sender)

- sender_deleted_status (sender)

- recipient_deleted_status (recipient)

- recipient_responded_status (sender)(recipient)

 

Please feel free to enlighten me on how to set things up!!  (I would be curious to know how PHPFreaks does things too...)

 

Thanks,

 

 

Debbie

 

[scootstah]

(I would be curious to know how PHPFreaks does things too...)

 

So download SMF and start reading.

[doubledee]

(I would be curious to know how PHPFreaks does things too...)

 

So download SMF and start reading.

 

I could.

 

And now back to my Original Thread/Questions...

 

 

Debbie

 

 

[AyKay47]

Well, scootstah broadly answered your last inquiry:

 

I would be curious to know how PHPFreaks does things too..

 

PHPFreaks uses SMF, if you research SMF you will understand how this website is set up.

 

But to your questions, this kind of database set up is more of a common sense ordeal than anything else really.

The normalization here depends on the private message logic (what features you want to have incorporated in the PM functionality.)

I see that you have already thought about the features and have laid them out, this is good.

 

Private Message:
- id
- sender_id
- recipient_id
- subject
- body
- sent_on
- new_message_status (recipient)
- recipient_read_status (sender)
- sender_deleted_status (sender)
- recipient_deleted_status (recipient)
- recipient_responded_status (sender)(recipient)

 

You never want to have too many fields in a single table that aren't necessary for the core functionality and can increase dependency.

In your case, given the data in this thread, I would break this into 2 (or possibly more) tables.

I would have the core private message data in one table:

 

- id
- sender_id
- recipient_id
- subject
- body
- sent_on

 

The above data is the essential data needed for a message, the other data is simply nice features that are not needed in the main table. so the second table:

 

- new_message_status (recipient)
- recipient_read_status (sender)
- sender_deleted_status (sender)
- recipient_deleted_status (recipient)
- recipient_responded_status (sender)(recipient)

 

Of course, there would be a few relations needed to link the first and second tables:

1. the message id

2. the recipient id

3. the sender id

 

The "message id" would be the primary key (pk) and would link to the `id` field in the first table.

The other two fields would be foreign keys (fk's) linking to the recipient and sender id's respectfully.

I might even break the second table into smaller tables separating them by each individual functionality to simplify things even further, but I will leave this up to you to decide.

[doubledee]

But to your questions, this kind of database set up is more of a common sense ordeal than anything else really.

The normalization here depends on the private message logic (what features you want to have incorporated in the PM functionality.)

I see that you have already thought about the features and have laid them out, this is good.

 

Private Message:
- id
- sender_id
- recipient_id
- subject
- body
- sent_on
- new_message_status (recipient)
- recipient_read_status (sender)
- sender_deleted_status (sender)
- recipient_deleted_status (recipient)
- recipient_responded_status (sender)(recipient)

 

You never want to have too many fields in a single table that aren't necessary for the core functionality and can increase dependency.

In your case, given the data in this thread, I would break this into 2 (or possibly more) tables.

I would have the core private message data in one table:

 

- id
- sender_id
- recipient_id
- subject
- body
- sent_on

 

The above data is the essential data needed for a message, the other data is simply nice features that are not needed in the main table. so the second table:

 

- new_message_status (recipient)
- recipient_read_status (sender)
- sender_deleted_status (sender)
- recipient_deleted_status (recipient)
- recipient_responded_status (sender)(recipient)

 

Of course, there would be a few relations needed to link the first and second tables:

1. the message id

2. the recipient id

3. the sender id

 

The "message id" would be the primary key (pk) and would link to the `id` field in the first table.

The other two fields would be foreign keys (fk's) linking to the recipient and sender id's respectfully.

I might even break the second table into smaller tables separating them by each individual functionality to simplify things even further, but I will leave this up to you to decide.

 

I'm sorry, but you totally lost me.

 

The first thing I need a handle on is what are the Database Entities that I am modeling?

 

Looks like you agree I need a "message" table?!  And I assume that I will need my existing "member" table??

 

But then again, do I need a table for the "Sender" and one for the "Recipient"?

 

What are the relationships?

 

 

Debbie

 

[AyKay47]

Looks like you agree I need a "message" table?!  And I assume that I will need my existing "member" table??

 

yes, the member table is needed to build relationships/references.

 

But then again, do I need a table for the "Sender" and one for the "Recipient"?

 

What are the relationships?

 

no you do not need separate tables for just that data, the sender and recipient fields in the `message` table will relate to the member id field of the `member` table.