Normalization and PM's

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…


MEMBER table
- id
- email
- username
- first_name

and so on...


PM_DISTRIBUTION table
- id (PK)

- private_msg_id (UK1)
* sender_id (UK2)
- recipient_id (UK3)

* sender_flag
* sender_deleted_on
* sender_purged_on

- recipient_flag
- recipient_read_on
- recipient_deleted_on
- recipient_purged_on

- created_on
- updated_on

[I](Fields with an asterisk ( )* denote those that used to be in the PRIVATE_MSG table, but were moved here.)[/I]


PRIVATE_MSG table
- id
- subject
- body
- created_on


Side Note:
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.


Questions:
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!! :slight_smile:

Sincerely,

Debbie

I would do:

private_msg


id
sender_id
subject
body
created_date
sent_date (if you want draft status?)

private_msg_recipients


id
private_msg_id
member_id
flagged
read
deleted
purged

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?

Here is what I ended up going with…

member


id (PK)
email (UK)
username (UK)
first_name
and so on...

private_msg_recipient


id (PK)
private_msg_id (UK1)(FK)
recipient_id (UK2)(FK)
recipient_flag
recipient_read_on
recipient_deleted_on
recipient_purged_on
created_on
updated_on

private_msg


id (PK)
sender_id (FK)
subject
body
sender_flag
sender_deleted_on
sender_purged_on
created_on
updated_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…


* sender_flag
* sender_deleted_on
* sender_purged_on

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.

Sincerely,

Debbie

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

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…

Sincerely,

Debbie

private_msg


id (PK)
sender_id (FK)
subject
body
created_on
updated_on

private_msg_recipient


id (PK)
private_msg_id (UK1)(FK)
recipient_id (UK2)(FK)
recipient_flag
recipient_read_on
recipient_deleted_on
recipient_purged_on
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”:

private_msg


id, sender_id, subject, body, created_on, updated_on
1, 1, "Hello, DoubleDee", "Test body", 2013-12-03 13:27:23, NULL

private_msg_recipient


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

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.

private_msg
id (PK)
sender_id (FK)
subject
body
created_on
updated_on

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.

private_msg_recipient
id (PK)
private_msg_id (FK)
recipient_id (FK)
folder – for example, inbox, sent items, or perhaps even a user-defined folder
flag
read_on
deleted_on
purged_on

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.

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.

Okay.

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!) :wink:

If I was going to use your suggestion, I’d probably tweak it like this…


id (PK)
private_msg_id (FK)
member_id (FK)
pm_member_role (e.g. Sender, Recipient)
flag
read_on
deleted_on
purged_on

Yep, it looks like you two are on the same page.

Sincerely,

Debbie