doubledee — 2013-11-25T10:42:44-05:00 — #1
I am working on my Private Messaging module, and have re-worked some things after it was pointed out that maybe I had some normalization issues.
In my new design, I would like the following...
a.) 3rd Normal Form (3NF).
b.) Ability to message multiple Users.
c.) Ability to store things efficiently
d.) Allow the Sender and Recipients of the PM to "flag", "delete" and "purge" a PM independently of what others do.
Here are the Relationships...
MEMBER (Sender) --||------|<-- PM_DISTRIBUTION -->|-------||-- PRIVATE_MSG
MEMBER (Recipient) --||------|<-- PM_DISTRIBUTION -->|-------||-- PRIVATE_MSG
Here is my Table Layout...
and so on...
- id (PK)
- private_msg_id (UK1)
* sender_id (UK2)
- recipient_id (UK3)
[I](Fields with an asterisk ( )* denote those that used to be in the PRIVATE_MSG table, but were moved here.)[/I]
My PM module works like most Email/PM systems do....
The Sender sends a PM and it goes into his/her "Sent" folder. If the Sender deletes it, the PM gets a "deleted_on" time-stamp and visually moves to the Trash folder. If the Sender empties his/her Trash, the PM gets a "purged_on" time-stamp and visually disappears.
Similar logic for Recipients.
And when both Sender and Recipient have "purged" a PM, then the record in PRIVATE_MSG (Parent) plus the record in PM_DISTRIBUTION (Child) are removed from the database.
1.) Are the Tables above in 3NF?
2.) Will this new design meet the above stated goals?
3.) Any design concerns with the Table Layout above?
I am 95% certain that this new design meets all of my goals and is well designed, however, some confirmation for you Database Gurus would make me feel better!!
kylewolfe — 2013-12-02T16:44:39-05:00 — #2
I would do:
sent_date (if you want draft status?)
Why do you need created and updated dates on the recipient table? If you are storing an update to the message itself, that belongs on private_msg, same goes for sender_id. You can look up msg create and send date based on the parent.
Am I missing anything?
doubledee — 2013-12-03T11:14:57-05:00 — #3
Here is what I ended up going with...
and so on...
The big thing I was looking for some validation on is this...
In my original design, I was coerced by others to put these fields in the private_msg_recipient table...
That is wrong, because if the Sender sent a PM to 3 people, and the Sender flagged the Sent PM in his/her Message Center, then my code would have to check the "sender_flag" in 3 records!!
That is denormalized!!
I would argue that the "sender_id", "sender_flag", "sender_deleted_on", and "sender_purged_on" are all intrinsically part of the Private Message!!
My style is to always have those two fields on any table, because every table has records that are created and usually updated. (It's a back-end auditing thing.)
Most enterprise systems do what I am doing for auditing purposes.
Admittedly, it doesn't make as much sense here, because you should not be updating the Recipient table, but the benefits outweigh the storage of one extra field.
I need to store "deleted_on" and "purged_on" for both the Sender and all Recipients. See my design above.
kylewolfe — 2013-12-03T11:43:04-05:00 — #4
I just want to make sure I've got everything straight. What is sender_flag? and recipient_flag? Just like a star?
If this is the case, I would remove flag, purge and deleted from the private_msg table. When a user sends a message, create an additional record in recipient for them. Ie. If kwolfe pms doubledee, one record in private_msg, two records in recipient (one for kwolfe). You then build the users "sent folder" from an inner join of private msg and recipient table, and they can star, delete and purge just the same. You're clean up would then be done if all recipients for a msg_id are purged.
This idea only works if you want to prevent user from sending a pm to themselves, but you could allow a draft for convenience as I previously suggested
doubledee — 2013-12-03T13:07:28-05:00 — #5
A Member can flag a Message of interest and a little flag icon appears next to it in either the Inbox or Sent views.
What is the advantage of that?
Not to be a pain, but it would be easier to follow you if you could post the layouts of the Member, Private_Msg_Recipient, and Private_Msg tables...
You lost me on that last part...
kylewolfe — 2013-12-03T13:38:58-05:00 — #6
created_on (again, unless you are allowing someone to edit this, these aren't needed)
updated_on (again, unless you are allowing someone to edit this, these aren't needed)
Example of data after K. Wolfe (1) sending message to DoubleDee (2) as the only "to":
id, sender_id, subject, body, created_on, updated_on
1, 1, "Hello, DoubleDee", "Test body", 2013-12-03 13:27:23, NULL
id, private_msg_id, recipient_id, recipient_flag, recipient_read_on, recipient_deleted_on, recipient_purged_on
1, 1, 1, FALSE, FALSE, FALSE, FALSE
2, 1, 2, FALSE, FALSE, FALSE, FALSE
Building a users "sent box":
select * from private_msg pm inner join private_msg_recipient pmr on pm.id = pmr.private_msg_id where pm.sender_id = $userId
Building list of messages to be purged from system (by your rules, when everyone has purged_on)
select id from private_msg pm left join (select private_msg_id as id from private_msg_recipient pmr where recipient+purged_on = FALSE) t on pm.id = t.id where t.id = NULL
Again though, all you are saving yourself is 3 columns on pm and enforcing yourself to prevent users from pm'ing themselves. If you want to allow them to pm themselves, then having your flags on pm is the way to go
jeff_mott — 2013-12-03T17:45:58-05:00 — #7
I'll add that I think there's a lot of merit in the direction that KWolfe is trying to push you in.
Notice, Debbie, that your private_msg and private_msg_recipient tables currently have a good amount of duplicated functionality, such as flags, deleted on, and purged on. You could try separating the concept of the message itself from the delivery of the message. Here's how I might structure it.
There's no flags and no deleted or purged in this table, because we're just thinking of the private message as an independent thing. We're not yet trying to place it in any kind of context, such as someone's inbox or sent box.
Then another table can represent the delivery of the message into various mailboxes.
folder -- for example, inbox, sent items, or perhaps even a user-defined folder
This way, you represent a sent item the same way you represent an inbox item, and functionality such as flagging, read on, deleted on, purged on, etc., all exist in only one place.
EDIT: Aside from the "folder" column that I introduced -- which would allow user-defined folders but is otherwise optional -- I think this is the exact same setup that KWolfe put together.
doubledee — 2013-12-03T20:44:11-05:00 — #8
What I think is more important, is that our independent designs are more similar than dissimilar...
"Good amount" is a bit of an exaggeration. Really the only difference between my latest design and your and Kyle's is 3 tables.
Unless I allowed Custom User Folders, the "folder" field is redundant, because "deleted_on" and "purged_on" are all I need to know whether a Message goes into the "Incoming", "Sent", "Trash" views or disappears completely. (Well, in my design that is true, at least.)
True, but here are some things to consider...
1.) "read_on" doesn't apply to the Sender, so that is a minor half-truth in this design
2.) "recipient_id" is no longer an accurate field name in your design.
3.) I suppose your "folder" field does this, but you really need a way to tell what "role" the person in this table is playing (e.g. Sender, Recipient)
4.) It's a bit confusing if a person PM's themselves with your design.
5.) Your design requires that I have Database Logic or Application Logic to prevent a PM from having multiple Senders.
6.) What happens when someone wants to "Forward" a Message?
Now you would have to have multiple "Senders" for a particular Message...
So which one came first? (Yeah, you could look at the "created_on" field, but that is somewhat messy. (Oh wait, there is no "created_on" field in Kyle's design!)
If I was going to use your suggestion, I'd probably tweak it like this...
pm_member_role (e.g. Sender, Recipient)
Yep, it looks like you two are on the same page.