Solved

Are these tables Normalized?

Posted on 2011-09-13
5
447 Views
Last Modified: 2012-05-12
Hi,


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
Customer ID, Contact Person, Address 1, Address 2, Telephone (1 and 2), Email (1 and 2).

Invoice
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
Labor ID, Labor Description, Labor Rate, Labor StartTime, Labor EndTime

Cust-Equipment
Cust_ID, Equipment_Type, Serial Number, Date of Purchase, Warranty Expiration, Manufacturer


Thank you.
0
Comment
Question by:epichero22
[X]
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
5 Comments
 
LVL 18

Accepted Solution

by:
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
schema.png
0
 
LVL 42

Expert Comment

by:dqmq
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.
   



 
0
 
LVL 85
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.
0
 
LVL 30

Expert Comment

by:hnasr
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.
http://support.microsoft.com/kb/283878

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)
telephones....
emails ....

Good luck!
0
 
LVL 11

Author Closing Comment

by:epichero22
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.
0

Featured Post

Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

Question has a verified solution.

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

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

696 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