Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

As part of my new and upcoming project, part of the business logic involves sending each and every one of my customers (assume they number in their 1000s) multiple (possibly customized) emails.

Each of these emails will have a:

  • Subject
  • To Field (can take list of emails)
  • CC Field (can take list of emails)
  • BCC Field (can take list of emails)
  • Email Body

The Email body will be HTML, and will have in it placeholders where my code can inject values (e.g. a placeholder for Order No, where the code will then inject the Order No for that customer).

If the Customer is new, he/she will receive an extra email (making the total three), if on the other hand they are an old customer, he/she will only receive two emails.

Certain customers might wish some different information displayed on their emails, so my database design need to account for this. E.g. Customer John Smith might want his emails to also list the 'Item Color', where as Emma Watson is not too fussed about that detail, and does not want that listed in the emails.

If customers have not explicitly mentioned that they want customized emails sent, then a Standard Template email is sent.

I believe this is a Many to Many relationship between the Customers and the Emails, so my ERD is very simple! After examining my business logic above, have I missed anything in the ERD below?

ER Diagram

Thank You.

UPDATE: ERD updated AGAIN after Joel Brown's input.

share|improve this question
1  
This "ERD" doesn't allow anything other than storage of customers and emails. Many customers can have many emails. If that's what you want to store, then you're golden. –  Max Vernon Nov 6 '13 at 18:07

1 Answer 1

up vote 2 down vote accepted

There are a few issues with your ERD.

A. Your ERD does not account for these business requirements:

Certain customers might wish some different information displayed on their emails, so my database design need to account for this. E.g. Customer John Smith might want his emails to also list the 'Item Color', where as Emma Watson is not too fussed about that detail, and does not want that listed in the emails.

If customers have not explicitly mentioned that they want customized emails sent, then a Standard Template email is sent.

You need to add a location to maintain your customer email preferences. The model for that depends on what the rules are for recording the preferences. Your question isn't explicit enough on this point.

B. You might also want to include an EMAIL_TEMPLATE table with a foreign key from EMAIL to this new table. That will allow you to record which template was used for each email.

C. You aren't clear about what your m:n relationship is recording. Are these columns: EMAIL.TO, EMAIL.CC, EMAIL.BCC foreign keys? If so, are they single or multivalued? (i.e. can you have multiple email addresses in any or all of these fields?) If they are single valued foreign keys, then you can do away with the many-to-many and make them each a many-to-one from EMAIL to CUSTOMER (so you will have three 1:m relationships) If they are multi-valued foreign keys, then you need to create an explicit intersection table instead of using a simple m:n relationship and you will want to have an ADDRESSEE_TYPE column ("TO", "CC", "BCC") as part of the composite key.


EDIT: Entity Relationship Diagram

This would be a logical data model that could support your business rules:

ERD

The TEMPLATE_FIELD_TYPE table is optional. You could use it if you have a standard list of fields that can be added to a template. If you don't want to lock down this list or reuse fields then you can skip this table and just use TEMPLATE_FIELD by itself.

share|improve this answer
    
Why doesn't my ERD account for the quoted requirements? If a customer wants different information, the he/she simply creates a new template with whatever information they need ... no? –  Ciwan Nov 8 '13 at 16:23
    
With regards to point C, I thought I'd store the emails a continues string where each email is separated by a semi-colon, is that not good? –  Ciwan Nov 8 '13 at 16:33
1  
@Ciwan - Different template: how do you know what template is used by an email? There is no template table for the email to point to. How do you know what information is in the email template? (i.e. what data-driven fields?) Where is the list of available fields? Where is the mapping of fields to templates? Re: point C. storing multiple delimited strings in a big string field probably does not conform to the rules of normalization and is probably a bad practice, except in certain specific circumstances (which are documented well on this site and are beyond the scope of a comment). –  Joel Brown Nov 8 '13 at 23:13
    
Thanks Joel, I get it now, and have updated my ERD accordingly. As for the list of available fields, I'll have a list showing all the fields, so that the admin doing the CRUD operations on the emails can see what is available to him or her. –  Ciwan Nov 12 '13 at 9:27
    
For the CC, BCC and TO lists, what do you suggest? a separate table for each? Wouldn't that be too much? –  Ciwan Nov 12 '13 at 9:28

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.