I work for a company which sends a newsletter to their customers as well as christmas e-cards. Both are sent by email. We are using an old database which was not properly designed and I have to normalize it. No one in the office knows how to use Access properly, including myself, so I did a fair bit of reading online and followed a few tutorials in order to familiarize myself with DB design. The DB is also used as a contact database with phone numbers and addresses for customers.
Now, the old database is a single table which lists every customer in its own row, with information such as their organization, address (sometimes personal, sometimes the organization's, phone number (work, personal, cell phone, etc...), email address, assigned employee, etc., all of them as attributes of the customer. Naturally, since many customers are part of the same organization, there is a lot of redundant data (organization contact info mostly). Now here are the rules on which I try to redesign my database:
- A customer may or may not be part of an organization
- A customer can only belong to one organization, if they do belong to one
- Many customers can belong to the same organization
- An organization may or may not have have linked customers (basically, an organization CAN be a customer in itself, but not necessarily)
- Some customers provide provide a personal email address (either at work or at home) or sometimes the main email address of the organization is provided. It is important to differentiate between those email address because the greetings reflects the kind of addressee (either Mr. xxxx, Ms. xxxx, or a generic greeting for a whole organization).
It seemed to make sense to me to separate customers and organizations in their own tables, linking them with an optional one-to-many relationship. However, when it comes to emails, this gives me a problem. I cannot put email addresses as an attribute in the customers and organizations table, because each email address needs to have two boolean attributes itself: whether the customer OR organization wishes to receive our newsletter/christmas card on that email. Therefore, emails have to be in their own table. However, since customers and organizations are each in their own table, I don't think I can use their primary keys as foreign keys in the emails table (like having one row linking to an organization and the next linking to a customer). But they are all emails, and I would like them to be unique and make sure there is no duplication (basically if a customer unsubscribe from the newsletter, is it important that this emails exists only in ONE place to make sure that they stop receiving the emails, also to prevent an email being sent more than once at the same email address), so I'm not too sure about making two separate tables for emails: organizations and customers.
Here is my ERD so far:
I'm not sure where and how to plug in the emails table. I'm also not sure about the ERD in general, this is my first time and I might have overlooked something completely. Any input on the matter would be greatly appreciated!
Guillaume