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!

From a normalization standpoint the second would be better.

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

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?

Uh... what language interface on what backend?

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