This might not exactly be a "normalization" question, it's more the type of data which I am saving.

I've just done a specification for a messaging and email system . The idea is that I need to save all of the messages which are internal to my web service, but als开发者_Go百科o know if an email has been sent with that message.

Here is the specification.

Specification

• Any messages are stored in one table.

• Messages can be from unregistered users, or registered users.

  1. An unregistered user message will just have a return email address

  2. A registered user message will have the user id of the sender

• Messages are either owned by a User (meaning that they are the sent to) or messages are shared by user roles.

  1. When a message is owned by a user, we record some information about this message (same table as the message).

     a) Has the user opened/read the message?

     b) Was an _email sent_ to the owner of the message or is it just an internal message

     c) Date the message was first read

     d) Date the message was sent

  2. When a message is sent to a group of users, meaning that they are sent to "All Users", or "All Owners" or "All SuperAdmin"...

     a) The message is saved once in the messages table with a sent date

     b) Each individual open is tracked in a seperate table

     c) A field records if a direct _email has been sent_, or if it is just saved internally in the system. (seperate table)

• Messages can be threaded, this means that if a message is responded to, that it is a child or the original message.

• Messages have different "Types", meaning that a message can be "System Notice", "Enquiry", "Personal Message", "Private Message", "Transactional Information"

• Messages which are linked to an enquiry for a product, will save the ID of the product they are enquiring for. (ie The relevant property).

End Specification

Now the actual question...

As you can see in bullet 1)(b) I am recording for a message which is sent to an indiviual user, if an email was also sent for that message.

However, when an email is sent to a group of users, I am then recording whether an email was sent in a completely different table. Obviously because I can't save this information in the same table.

What are your opinions on this model. I'm not duplicating any data, but I'm seperating where the data is saved. Should I just have a email_sent table to record all of this information.

It is hard to say whether your current design is good or bad. On the surface, I think that it is a mistake to separate the same piece of information into two places. It may seem easier to have a note about an individual email sent in the table which is closer to the individual and notes about emails sent to groups closer to the groups. However, your code is going to have to go looking in two places to find information about any email or about all emails in general.

If the meaning of the flag email_sent is the same for an individual user as it is for a member of a group of users, then looking in two places all the time for what is essentially one kind of information will be tedious (which in code terms comes down to being potentially slow and hard to support).

On the other hand, it may be that email_sent is something that is not important to your transactional or reporting logic and is just a mildly interesting fact that is "coming along for the ride". In this case, trying to force two different email_sent flags into one place may require an inconvenient and inadvisable mash-up of two entities that ought to be distinct because of all of their other, more important attributes.

It is difficult to give a conclusive answer without having a better understanding of your business requirement, but this is the trade-off you have to consider.

Create 3 tables:

1. MSG with id (key auto), msgtext, type (value U or R), userId/roleId

2. ROLES with roleId, userId

3. ACCS with userId, MsgId, date opened, read, etc

MSG records the message, with a type to see if it's from a role or unregistered user

ROLES points one role to many users

ACCS records everything, for a user, registered or not.

To retrieve, join the MSG type U with ACCS

join MSG type R with ROLES and then with ACCS

To retrieve all, UNION them