2 Added ERD to clarify the advice.
source | link

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.

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.

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.

1
source | link

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.