Results 1 to 6 of 6
  1. #1
    gsoucis is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2015
    Posts
    4

    Design for emails table in a contacts database for sending newsletter


    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:

    Click image for larger version. 

Name:	Capture.JPG 
Views:	17 
Size:	56.6 KB 
ID:	23123

    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

  2. #2
    llkhoutx is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Jan 2010
    Location
    Houston, Texas USA
    Posts
    373
    Link Persons.Pers_id to Emails.FKPersId.

  3. #3
    gsoucis is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2015
    Posts
    4
    Quote Originally Posted by llkhoutx View Post
    Link Persons.Pers_id to Emails.FKPersId.
    Did you read my post? Emails also belong to organizations...

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    You used main_phone for Organization, how about main_email????

    You also mentioned Customers and Contacts within the post, but you have Persons as the table name.
    Is there something different between Person and Customer? Or Contact?

  5. #5
    gsoucis is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2015
    Posts
    4
    Quote Originally Posted by orange View Post
    You used main_phone for Organization, how about main_email????

    You also mentioned Customers and Contacts within the post, but you have Persons as the table name.
    Is there something different between Person and Customer? Or Contact?
    When talking about a "customer" in my post, it is indeed a person, same thing. For reason why I am not using main_email, the last paragraph right before my ERD explains this prettywell: I cannot have an attribute which itself has an attribute. And I mentioned the word contact saying that it is also used as a "contact database", which means the information is used to CONTACT people (i.e. we have their contact information).

  6. #6
    gsoucis is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2015
    Posts
    4
    Any ideas?

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Editing Contacts connected to Bounced Emails
    By Ana.Amalfi in forum Access
    Replies: 1
    Last Post: 01-20-2015, 12:36 PM
  2. Database design help for multiple contacts
    By newbieX in forum Database Design
    Replies: 6
    Last Post: 12-09-2013, 06:54 PM
  3. VBA help - Sending report to contacts from a query/table
    By programmer_novice in forum Programming
    Replies: 1
    Last Post: 10-10-2012, 12:57 PM
  4. Sending emails from Acess Database
    By Lirizarry in forum Access
    Replies: 3
    Last Post: 01-26-2012, 10:04 PM
  5. Newsletter Database
    By STENTOFON in forum Import/Export Data
    Replies: 0
    Last Post: 07-29-2009, 07:03 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums