1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Help on design of customer database in Access 2007

Discussion in 'Business Applications' started by bekah3475, Feb 17, 2009.

Thread Status:
Not open for further replies.
Advertisement
  1. bekah3475

    bekah3475 Thread Starter

    Joined:
    Feb 17, 2009
    Messages:
    5
    I am a beginner trying to design a customer database in Access 2007 for my company. They sent me to 2 full days of classes on Access and I am still having trouble.

    I mostly understand all of the basic concepts of Access, but the design part is hard for me. I half understand the point of normalizing and am having trouble with the concept that nothing can be repeated.

    Every customer has a different amount of information. Some customers have 2 addresses (physical and mailing), some don’t. Some customers have 5 email addresses and 3 phone number and some have 1 email address and 1 phone number.

    In my database, I want to include:

    -Customer name
    -Address(es)
    -Phone number(s)
    -Fax numbers(s)
    -Email(s)
    -Contact Person(s)
    -Notes Section

    Pretty much the same info as a contact card in outlook, but in database form.

    So from what I learned in class, my understanding is that anything that has more than one option should be made into a separate table. Then you should link them up through the relationships. What is the most efficient way to design this?

    Please help! My boss thinks that this should be a super easy task and it’s not for me…
     
  2. rbalaji

    rbalaji

    Joined:
    Feb 5, 2009
    Messages:
    328
    You have to analyze the relationship between different types of information in the database to figure out how to design the tables appropriately. For example, if one customer can have many email addresses, then there is a one to many relationship between customer and email address. This means that you can't just add email address to the customer table, but instead have a related table for email addresses so that you can use a customer id to relate a customer to however many email addresses they may have.

    So, in your example above, I would have a customer table and a separate table for addresses, phone numbers, fax numbers, email addresses, contact persons and perhaps for notes also (in case you can have different notes about different aspects of a customer or have a notes field in each of the related tables so that you can figure out what type of address something is or what type of phone number something is and so on). The customer ID will be the primary key in the customer table and will be a foreign key in all the other tables.
     
  3. bekah3475

    bekah3475 Thread Starter

    Joined:
    Feb 17, 2009
    Messages:
    5
    Should all tables be connected by only 'CustomerID'? If I had separate tables for customers, addresses, phones, fax, emails and contacts, would they all be connected to 'customerID'. I wouldn't have any other relationships?
     
  4. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    Although rbalaji is correct - multiple tables, one for each type of information linked by the ID number is the correct way to do this, and and the easiest to edit.
    However, a semi-cheat - and potentially much easier way - is to just put everything into one table. Assume some things - no one will have more than two primary phones, two mobile phones, two fax numbers, 3 address fields, potentially 3 cities, 3 states...etc.
    The problem is that if someone has more than you have allowed for, you have to insert a new field for that (and likewise for any forms, tables, etc.) and it is easy to get messed up.
    But for quick and dirty, this would work.
     
  5. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    Yes, that would be the only connection. Every customerID would be unique, so it would carry across to connect only the correct data.
     
  6. bekah3475

    bekah3475 Thread Starter

    Joined:
    Feb 17, 2009
    Messages:
    5
    Hmmm...I think I am making this more complicated than it has to be. Can you take a look at my relationships and see if this is how they should be?
     

    Attached Files:

  7. rbalaji

    rbalaji

    Joined:
    Feb 5, 2009
    Messages:
    328
    Once you have separate tables for addresses and phone numbers, you don't need repeating groups in them. Each row in addresses for example would just consist of customer ID, street address, city, state, zip and country and a type. The same customer ID can have multiple addresses in that table with different types (or even the same type). The way you have it set up, you are still restricted to just two addresses for a customer which is precisely what you wanted to avoid by going to this more complicated set of tables.
     
  8. bekah3475

    bekah3475 Thread Starter

    Joined:
    Feb 17, 2009
    Messages:
    5
    O! I understand. This will prevent repeating entries.

    Do I need to have an Address ID, PhoneID, FaxID, EmailID and ContactID for each of the tables, or is this excessive? Should I link each ID back to tblCustomers via relationships?
     
  9. rbalaji

    rbalaji

    Joined:
    Feb 5, 2009
    Messages:
    328
    You don't need addressID, phoneID etc., but in general it may be a good idea to have such a field as the primary key of each of those tables. These ID's should not be linked back to the customers table. In a one-to-many relationship, only the primary key from the one table is used as the foreign key of the main table, not the other way around.
     
  10. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/801758

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice