Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2004
    Posts
    4

    how far to normalize this table? (normalization question)

    I have a VENDOR table. Each vendor can accept different forms of payment AND can offer different kinds of services. Currently, there are 4 types of services being tracked and it is unlikely to change in the future BUT it is possible.

    I see two options.

    OPTION ONE - I could have one table with these fields:

    VENDOR
    vendor_ID
    name
    address
    ...
    service1 (Yes/No)
    service2 (Yes/No)
    service3 (Yes/No)
    service4 (Yes/No)
    payment1 (Yes/No)
    payment2 (Yes/No)
    payment3 (Yes/No)


    OPTION 2 - I could break this down into 5 tables, VENDOR, SERVICE, PAYMENT, VENDOR_SERVICE, VENDOR_PAYMENT:

    VENDOR
    vendor_ID
    name
    address
    ...

    SERVICE
    service_ID
    service_Name

    PAYMENT
    payment_ID
    payment_Name

    VENDOR_SERVICE
    vendor_ID
    payment_ID

    VENDOR_PAYMENT
    vendor_ID
    payment_ID



    Option One makes coding the PHP pages SO much easier. however, I want to make it right from the beginning.

    Your experienced comments are very welcome and much appreciated!

  2. #2
    Join Date
    Dec 2003
    Location
    Canada
    Posts
    710
    From a normalization standpoint the second would be better.

  3. #3
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    From a development standpoint the second is better to. With that schema, you can do things like referencing the payment table in your webapps to get a list of allowable payments. Then if you decide to make another payment type available, you don't have to run back and recode every page to display the new payment type. There's all sorts of nice automated stuff you can do this way.

    I would suggest attaching arbitrary keys to ALL the tables though, even your detail stuff. That's part DBA supersticion and part functional flexibility. Should you ever decide to reference items from the vendor_service table as foriegn keys, you'll be all set. Eg:


    VENDOR_SERVICE
    vendor_service_id
    vendor_ID
    payment_ID
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  4. #4
    Join Date
    Dec 2004
    Posts
    4
    thanks. I've started on it and like it now that I know how to code the SQL correctly.

    Next challenge is to code the edit - it's going to be a multi-table edit page.

    For example, I will have to display in checkboxes ALL the payment types and check all the ones that are active for that vendor. Then when the page is updated, I suppose I will have to delete all references for that vendor in vendor_payment and add new records. That would probably be easier that coding a check of each record upon edit submission.

    Thoughts?

  5. #5
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Uh... what language interface on what backend?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  6. #6
    Join Date
    Apr 2004
    Location
    Toronto, Canada
    Posts
    249
    One simple way to do this is simply to insert the data and ignore the error.

    For example, let us assume that Vendor A provides services S1, S2 and S4.

    The UI displays the appropriate page, the user checks the fields for S3 and S6, and you have to save this information.

    Since you know that S1,S2,S3,S4,S6 are checked, you can just go ahead and blindly insert these records, and ignore the errors that result when attemptng to re-insert S1,S2 and S4.

    Or, you can build a list containing S1, S2 and S4 when you display the screen the first time. When the user wants to save the information, only send the values that are not in this list, in this case, S3 and S6.

    There are many simple ways to tackle this problem. Just use any one of them.

    Hope that helps.

    Ravi
    Last edited by rajiravi; 12-30-04 at 13:05.

Posting Permissions

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