• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 471
  • Last Modified:

Are these tables Normalized?


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.
1 Solution
Garry GlendownConsulting and Network/Security SpecialistCommented:
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
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.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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.
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!
epichero22Author Commented:
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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now