Database Design Question

I think I know the answer to this already, but I am going to ask for some opinions anyway.

Currently, there is a Clients table. We need to add 1 (or possibly 2) more tables to store the services that these clients get. My question is which way is better, and why.

Option 1)

Create a ClientServices table. Stuff the ClientID's in and set defaults on all the bit fields of services that can then be toggled (there will be upwards to 50 services)

ClientIDReportsSurveyDiagrams11012001

Option 2)

Create a Services table

ServiceIDName1Reports2Survey3Diagrams

And then create a child ClientServices table

ClientIDServiceID111323

Option 2 is the way that I usually design my own tables and here is why. When you need to build a front end or site, you can use the Services table to fill dropdownlists or other controls. Simply put, it makes it easier to write controls to either retrieve service names, or update a client's services. Also when it comes time to add a new service you only have to add a new record into the Services table. This way you willNOT have to update all of your stored procedures and queries to include a new service. The same is true for deletion. Sorry if it sounded like I was lecturin but I just learned this hard lesson a few months ago designing a online faculty directory for my employer.

|||

Thanks. That is how I was going to design it as well. The administration will basically be handled using Access (blah) and I won't be responsible for designing that form per se. There is no web front end to this thing.

Option 1 is how they are used to doing things, but I needed to be able to explain why Option 2 is better..

|||

I started out as just a programmer but working side by side with a database designer has taught me why they design databases the way they do. I have a huge DB containing all of the faculty member's unique information. And for every subcategory (Title, Department, Location, etc.) I have integers, and then I have other "child" tables that contain the descriptions and other fields assigned to the integers. This allows me to fill dropdownlists easily, and add new titles or locations on the fly. Good luck.