Hey guys.

I'm doing an internship in the States and I have to complete the following task.

An advisor inputs all invoices from the salesmen into excel sheets. first of all to calculate the commission for each salesperson, second of all for reporting.
How much commission was summed up today, weekly etc.

so far the excel sheets are almost not automated and it takes a lot of time to calculate it by zourself. i want to design a database with ACCESS 2003. I already made a form to input the following information.

- Date
- Salesperson
- Invoice #
- Customer
- Warranty period?
- Payment method
- purchase price
- sales price
- warranty price
- total sale
- credit card fee
- shipping
- commission

They do not want me to store any personal information about customers or the sale personnel. it is also not interesting what product was sold. all they care about are the numbers.

so far all these information are stored in ONE table. only those rates which might change, like credit card fees or commission rates, are stored in another table. the form i created accesses these data for their calculations. also i put the salespersonnel (id and name) in a table and a combobox on the form accesses this table. the last table contains thousands of customer (only company name) and a combobox also accesses this table. but none of these tables are referenced to each other.

so i know of normal forms and all that design rules, but i do not see a way to do that here. do you? i mean i certainly have the first normal form, but can i apply the third? or any other things that improve my db? can't wait to hear some advices for my design...

thank you very much

before you start worrying about normal forms, you have to have a natural key

what's your natural key here?

Well so far i did not think about that, but I think it is the invoice number. this numer has a logical relation to all other attributes...

well, that's a good start, but it's not enough, you're looking for a compound key here

the key of this table cannot be just invoice number, otherwise you would only be able to store at most one row for each invoice

thx a lot for your help r937

yes, i think an invoice# will show up several times once in a while, when customers buy several parts...

so i have to add another attribute to invoice number? and both attributes are the compund key? that makes sense... ok once i got this done, are there more things to improve?

is there anythying i need to do about the several items on one invoice, except for the compund key?

thx

i don't think you need to add a column

try to see if you can find unique combinations of invoiceno with one or more existing columns

oh yeah, that is what i meant. guess my bad english ruined that

i sure did find a second attribute for a combination..... invoice# and the kind of sale

So what would be the next step, if there is one? thx thx thx

which column is "the kind of sale"?

actually, i want to go back to post #4

i think i was wrong

there's only one row of data for each invoice, right?

in that case, i think you're okay with invoice number as the PK

there are two kind of sales. parts they sell in stock(regular sale) and those they buy somewhere else and sell then to customers (special order sale). i have to differentiate. this column is just a checkbox. if checkbox clicked then special order sale true. so there would me maximum two rows with the same invoice number. all sums from special orders are added up, as those from regular sales. means if 4 items were sold, 2 spo's and 2 regular, i add up both spo's in 1 row, the same with regular

so technically there might be two rows per each invoice...

so two columns, then

you're doing fine

okay, now here is the next step in the normalization

if i told you the invoice number and the sale type, e.g. 12345/reg, and then, somewhere else in that row, if i changed one piece of data, would anything else have to change?

for example, if i changed sales price, for that row, would i also have to change commission?

if the answer is yes, you have a non-key dependency

that is correct. if you changed sales prices for example, that would affect several other data.... so the answer would be yes..

well, that means that commission is dependent not just on invoice number and sales type, but on sales amount, too

so it's not in 3NF

see http://www.datawarehouse.com/article/?articleid=2992 for a good explanation

thx for the link..... i am sure i will have some questions after i read it