Solved

Are these tables Normalized?

Posted on 2011-09-13
5
391 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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
PL/SQL query 14 51
Max per month 3 15
Dlookup issue 4 15
DATETIMEOFFSET feature 1 0
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

746 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now