Are these tables Normalized?

Posted on 2011-09-13
Last Modified: 2012-05-12

I'm trying to design a database to keep track of my customers, the work I render and equipment I purchase for them.  I'm trying to divide the data components into separate tables but am not sure if this is correct.  Here it is:

Customer ID, Contact Person, Address 1, Address 2, Telephone (1 and 2), Email (1 and 2).

Invoice No, Customer ID, Labor ID, Equipment Description, Equipment Quantity, Equipment Price, Vendor Invoice Number, Commentary, Billing_Type (whether they prepay me for work or I have to invoice them after the work).

Labor ID, Labor Description, Labor Rate, Labor StartTime, Labor EndTime

Cust_ID, Equipment_Type, Serial Number, Date of Purchase, Warranty Expiration, Manufacturer

Thank you.
Question by:epichero22
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 18

Accepted Solution

Garry Glendown earned 500 total points
ID: 36534205
From the fields, I'd say it doesn't look too bad ... from personal experience, it's not always important to be normalized as far as possible, but doing what you need it to do ;) (though most of the times, both end up matching)
One thing I see is that with your current structure, an invoice will always only consist of one labor job, and will always only have one piece of equipment involved etc. - I don't know your requirements, but this might be prone to need a rework at some point ... also, what if there are multiple vendor invoices for a single job?
Without knowing further details, may I suggest something like the below schema ... (left out the data fields, just the index fields are shown)

- it allows multiple jobs in a single invoice
- multiple vendor invoices for single job
- multiple labor entries on a single job
- optionally (if not needed, the N:M relationship between labor and cust-equipment could be dropped) documentation of which equipment was worked on in a labor entry
LVL 42

Expert Comment

ID: 36534309
It's impossible to tell if your design is normalized without knowing the keys and functional dependencies. In large part, those things can only be discerned from requirements, not from a list of tables and columns.

Having said that, I do have some observations about your design.

1. I trust you have a primary key on each table; that would put your tables in 1NF.

2. While it doesn't pertain to normalization per se, I'm hoping phone 1 and 2 are separate columns.  Same for email 1 and 2.

3. Based on what I can assume from column names, I believe your design could be improved

    Add InvoiceID to Labor and remove LaborID from Invoice
    Create a separate table for Equipment instead of holding some equipment facts in the Invoice table and others in the Cust_Equipment table
    Add InvoiceID to Cust_Equipment and remove Cust_ID.

LVL 84
ID: 36535168
I won't speak to the topic of normalization, since it always devolves into a discussion that is non-resolvable and mired in minutea, and it detracts from what I preceive to be your intent (which is to correctly store your data for your real-world application):

I wouldn't store Phone1 and Phone2, and Email1 and Email2 in this manner. From a functional standpoint, you would be better off having a "Contacts" table, where you could store various types of Contacts for a single Customer - for example, MobilePhone, Email, Fax, WebSite, TextNumber, etc etc. With the explosion of different contact methods, it's conceviable that you would need to add other types of contacts, and if you store in the manner you suggest you'd be forced to add columns to your table (and items to your user interface) to accomodate those.

I would also consider adding a Master/Detail sort of a setup for the Invoice, as suggested earlier, however I'm not clear on what a "Job" is. Typically invoices simply record some form of financial transaction, and you don't really care whether that is a Job (i.e you providing a service) or a Sale (you providing equipment/material).

If you use a Master/Detail setup, you might also consider breaking out Labor to a Detail line by itself, although that's really a business decision and not a data one.
LVL 30

Expert Comment

ID: 36563277
It is not normalised.

It fails the First Normal Form.
Customer emails are repeating in one customer record. The same with addresses.

Check this Microsoft document.

Customer (Customer ID, Contact Person, Address 1, Address 2, Telephone (1 and 2), Email (1 and 2))
==>This leads to 4 or more tables.
Customer (Customer ID, Contact Person)
addresses (Customer ID, Address, AddressType)
emails ....

Good luck!
LVL 11

Author Closing Comment

ID: 36577958
Thanks, and I think I should start with some smaller application first and get comfortable with something of that caliber.  This is actually something I want to build into a small invoicing Access application but I think that I should learn it one step at a time.

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question