Solved

Are these tables Normalized?

Posted on 2011-09-13
5
415 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
5 Comments
 
LVL 17

Accepted Solution

by:
Garry-G 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 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.
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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Suggested Solutions

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
These days, all we hear about hacktivists took down so and so websites and retrieved thousands of user’s data. One of the techniques to get unauthorized access to database is by performing SQL injection. This article is quite lengthy which gives bas…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
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…

770 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