Redundant data, storing Quotation information.

[lbtaylor1984]

Hey guys.



I'm working on a database system to collect our Estimate paperwork at work.

Now I've tried to normalise the data as much as possible but i'm having trouble making out where to stop.

As some background information

We currently type our estimates up in a publisher template, and once the job is accepted and completed we type up a separate invoice in publisher.
The estimate no. and invoice no. have no relation and all the information is typed in by hand. Our services are calculated per job and the cost cannot be calculated by the database, this will not work for us. We currently suffer from Estimate and Invoice no duplication, and we also have errors in client information, due to this all being manually entered.

What i'm trying to do is make a database where we have a table with all our clients entered, which can be selected by our typist, and for all the estimate and invoice no's automatically generated to stop duplication. I've got as far as making the tables and setting up some forms for the system, now I understand I shouldn't have information duplicated in multiple tables, so my current issue is that if I do not make the estimate table store information from other tables into itself when I look through the records there will be blanks where the information should be, as it's obviously not being stored. but should I store this information twice and if not how do I make it remember the information for looking at a later date?

please see below what I've done and hopefully the explanations will reinforce what I've written above.

What I've done so far;

I've normalised the data as so.


KEY

[Table] Name <-Table and it's name
/name <-Field in table
{PK} <-Primary Key

[Table] Clients

This contains a unique reference autonumber
and is broken down into

/ClientID {PK}
/Name
/CompanyName
/Address
/Address2
/Town
/County
/Postcode/
Phone no
/Credit limit.

[Table] Estimate

This contains a unique reference autonumber
and is broken down into

/EstimateID {PK}
/Date
/SiteName
/SiteAddress
/SiteAddress2
/SiteTown
/SiteCounty
/SitePostcode

/WorkDescription
/CostTotal

((This site information is unique to the Estimate and is stored in the Estimate TABLE))

**Redundant Data in CLIENT TABLE

/ClientName
/ClientCompanyName ((ComboBox which fills out all other Client details))
/ClientAddress
/ClientAddress2
/ClientTown
/ClientCounty
/ClientPostcode
((If I do not include CLIENT detail fields in the ESTIMATE TABLE the information will not save what was selected via the combobox.))
/SalesPersonID
/AccountTerms
((These fields are in there respective tables, but again I have to add these fields to the Estimate TABLE or the information will be forgotten should you reopen the estimate.))


[Table] SalesPersonID
/SalesPersonID {PK}

[Table] AccountTerms
/AccountTerms {PK}


I have my relationships setup, and it seems that is all ok, So is duplicating the Client information, the Sales person and the Account terms ok? everyone keeps saying don't duplicate but if I don't I end up with a description of what we plan to do, the cost and the site address, but the client, the sales person and terms are all blank when you go through the records?

[Dal Jeanis]

You should store ONLY the KEY to the other records in the Estimate record. the key to the Client record, the Key to the Salesman record, the Key to the terms. And you only store the terms if they differ for each estimate, rather than for each customer. If each customer always gets consistent terms, which might be different for different customers, then you store a link to the terms on the customer record, not the estimate record.

You don't need the rest of the data to be duplicated, you pick up the details (like salesman name) by joining the appropriate records in the query.