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!!
Sincerely,
Debbie