I'm currently setting up a job board and need some advice on how I should setup some of the tables. I currently have a care_provider table care_provider provider_id email password etc... provider_type type_id type_name (example: 1:baby sitting, 2et Care, 3:Cleaning Services, etc...) services_provided type_id provider_id Now here's my dilemma. Each of the services share some basic form fields such as Years of experience, desired wage, do you smoke, etc... But then there are some form fields that are unique to each service such as if you offer pet care, there would be several check boxes asking what kind of pets you care for like dogs, cats, snakes etc... plus several other questions that were unique to that service. And let's say you offered house care, there would be a question asking if you could bring your own cleaning supplies, what cleaning services do you offer, can you supply your own equipment etc... So how should I setup the table structure? Should I add every possible field to the "services_provided" table? Should I create a table for each type of service that is offered and they each have their own set of fields? Should I put the shared fields in the "services_provided" and also create a table for each type of service that is offered for their unique fields? Should I do none of the above? I hope this makes sense and greatly appreciate any help. Thanks - Mike
I would try to get to 3rd normal form and put the shared fields in the services_provided table then create sub table for the various services. The sub tables should be "below" the services_provided table (foreign key from services_provided). Do some reading on database normalization supertypes and subtypes. That should help you get the right mix for your use.