Results 1 to 9 of 9
  1. #1
    sinisterfrog is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    23

    Normalization of Customer Informatio

    I'm in the beginning stages of writing a new database. Our existing database, while functional, lacks a sound design resulting in slow queries.



    I'm working on the design of the tables surrounding customer information. Our existing table looks something like below

    Customer table
    • First name
    • last name
    • Company
    • Department
    • Address line 1
    • address line 2
    • city
    • state / province
    • Zip/postal code
    • country
    • Phone Number
    • Email


    Where we suffer the most is when a customer changes location. We deal with academia and so our customers often end up in different univeristies / departments. What this leads to is either erroneous data being entered (shipping to someones undergrad university rather than where they are teaching) or in our current system we often "correct" the old data with their existing location (in other words, we lose track of where the person has been which can be useful).

    I'm in the process of normalizing the data and I'm hung up on addresses. All customers will have an address, all departments in companies will have addresses, not all customers will work for a company and therefore ship to their home.

    Any help (assuming you can read through my gibberish) would be appreciated.

    -Matt

  2. #2
    Join Date
    Apr 2017
    Posts
    1,673
    Step 1 (mandatory!): Add CustomerID to customers table. It may be autonumeric, and it will be primary key for table;
    Step 2: Split customer contact information into separate table (or several tables), with a date type field indicating at which date this contact information is valid.

    An Example.
    tblCustomers: CustomerID, FirstName, LastName, ...;
    tblCompanies: CompanyID, CompanyName, ...;
    tblCompDep: CDID, CompanyID, DepartmentName, ...;
    tblCustCompDep: CCDID, CustomerID, CDID, ValidFrom;
    tblCustAddresses: CAID, CustomerID, ValidFrom, Country, State, City, Address1, Address2, ZIP, [CurrentAddress]. (CurrentAddress is optional Boolean field);
    tblCustPhones: CPID, CustomerID, PhoneNumber, ValidFrom, [CurrentNumber] (Customer may have several phone numbers active at same time. CurrentNumber is Optional Boolean field);
    tblCustEmails; CEID, CustomerID, Email, ValidFrom, [CurrentEmail].

  3. #3
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    @Matt
    Note that Arvi removed spaces and special characters.


    • First name -------> FirstName or First_Name
    • last name --------> LastName or Last_Name
    • state / province --> StateProvince or state_province
    • Zip/postal code --> ZipPostalCode or Zip_Postal_Code





    Some suggestions:
    Use only letters and numbers (exception is the underscore) for object names.
    Do not use spaces, punctuation or special characters in object names.
    Do not begin an object name with a number.
    Do not use Look up FIELDS, multi-Value fields or Calculated fields in tables.

  4. #4
    sinisterfrog is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    23
    Thank you everyone. Are lookup fields just generally bad databasing?

  5. #5
    Join Date
    Apr 2017
    Posts
    1,673
    Quote Originally Posted by ssanfu View Post
    Do not use ... Calculated fields in tables.
    It is a general rule. In my example optional fields CurrentWahtever are calculated fields, but in certain situations they make data processing much easier. It depends on, how data are used and how forms are designed.
    Fully agree about lookup and multi-value fields though!

    Quote Originally Posted by sinisterfrog View Post
    Thank you everyone. Are lookup fields just generally bad databasing?
    Instead use combo boxes in forms, which get data from lookup tables - the functionality is same, ant you have less headaches. Really most of tables in my example are lookup tables (or registries, as I name them).

  6. #6
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Matt,

    Do you have a clear description of what this database is/will be used for? Have you identified the shortcomings of the current database and processes? Normalization and database design are not isolated things, they are parts of the "automating/support with automation" activities.
    Don't be too quick to jump into physical database. You can do a lot of testing/analysis and refining using pencil and paper; test data and some test scenarios.

    Here is a link to a variety of info on Database Planning and Design that may be helpful.

    Good luck with your project.

  7. #7
    sinisterfrog is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    23
    Thank you Orange for the articles~ Looks like I know what I'll be reading this weekend.

    As for the purpose of the database~ It's really to just upgrade our existing database and improve how it functions. I split the database earlier this year and all networked computers have slow query times. After attempting numerous fixes, I was able to decrease query times from ~5 min down to about 30 seconds. The sluggishness seems to be specific to summing data or counting results.

    I've attached a view of the relationships view. ~We lack primary keys, our tables are monolithic and I'm sure you will notice plenty of other issues.
    Click image for larger version. 

Name:	Capture.jpg 
Views:	26 
Size:	105.6 KB 
ID:	36459

    We're a small business so the database handles shipping/receiving, order entry, manages customers (poorly), payroll, invoicing and a handful of smaller tasks.

    -Matt

  8. #8
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Quote Originally Posted by sinisterfrog View Post
    Thank you everyone. Are lookup fields just generally bad databasing?
    See The Ten Commandments of Access
    Pay attention to #2 and follow the link.....


    I think (I'm sure) orange was asking more about "What is your business and what will the database will accomplish/How will the database help the business?".
    Right now we know zero about your business/processes.

  9. #9
    sinisterfrog is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    23
    We make thin sections (https://en.wikipedia.org/wiki/Thin_section). The db keeps track of orders and samples, due dates, production statistics, invoice amounts, payments.

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

Similar Threads

  1. Replies: 7
    Last Post: 08-06-2016, 07:05 PM
  2. Normalization
    By jlt199 in forum Database Design
    Replies: 10
    Last Post: 03-21-2014, 12:22 PM
  3. Replies: 6
    Last Post: 12-11-2013, 09:27 PM
  4. Replies: 1
    Last Post: 12-24-2011, 08:48 AM
  5. Normalization
    By KPAW in forum Database Design
    Replies: 1
    Last Post: 06-09-2011, 06:24 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