Results 1 to 14 of 14
  1. #1
    Join Date
    Aug 2007
    Posts
    13

    Can I use normal forms here?

    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
    Last edited by tom.fox; 08-24-07 at 12:46.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    before you start worrying about normal forms, you have to have a natural key

    what's your natural key here?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Aug 2007
    Posts
    13
    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...

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Aug 2007
    Posts
    13
    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

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Aug 2007
    Posts
    13
    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

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Aug 2007
    Posts
    13
    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...

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Aug 2007
    Posts
    13
    that is correct. if you changed sales prices for example, that would affect several other data.... so the answer would be yes..

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  14. #14
    Join Date
    Aug 2007
    Posts
    13
    thx for the link..... i am sure i will have some questions after i read it

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •