Large-scale, multi-use database design

I'm working on a project that has snowballed and I'm running into db issues that I never expected to face...

The client is opening a health spa. Multiple services will be offered - massage therapy, acupuncture, chiropractic, nutritionist, etc. - all provided by individual practitioners. The goal is that the sum will be greater than the individual parts by making it feel to the customer as though it's a single, cohesive, comprehensive facility. There will be centralized reception and scheduling and various other points of integration, probably including billing. One of the main goals of all this is for the various practitioners to help each other succeed through cross-promotion.

Because of this, I assumed a centralized database for all parties. Initially I designed a schema with a "business unit" field in various tables (products, services, appointments, etc.), using additional tables as necessary for proper normalization. But it doesn't feel right. But given the desire for cross-promotion and integration, multiple databases seems out of the question.

My latest thought is to use common clients/accounts tables and duplicate other tables using some naming convention like services_massage, transactions_chiro, products_nutri, etc.

Comments? Better ideas?

Thanks in advance!

You were doing great with normalization in mind but your latest thought is bad bad BAD!

This is what in my experience is the most appropriate approach:

- Add more fields and tables to ensure normalization:
Use an additional field to filter products/services belonging to a certain department. eg.:
DEPT(dept_id,name,description,whatever)
SERVICE(service_id,dept_id,name,description,price)
PRODUCT(product_id,dept_id,name,description,price)

Then, instead of a 'services_massage' table, you'll have to use a query to find records in the services table that 'belong to' the 'massage' department.
eg:

Code: Select all

 SELECT name,price FROM SERVICES WHERE dept_id=6
--where 6 is the ID of massage dept

- Use views:
Once you've done the above, you can create views to make your life easier.
eg.:

Code: Select all

 CREATE VIEW view_Services_Massage
BEGIN
 SELECT name,price FROM SERVICES WHERE dept_id=6
 --where 6 is the ID of massage dept

the you can actually do this:

Code: Select all

 SELECT name,price FROM view_Services_Massage

Hope that helps...

So I was barking up the right tree to begin with! It just seemed (never having done anything like this before) that the schema looked pretty funky with all those BU_ID fields. It makes a lot more sens, though, when you factor in the views.

Thanks for the help!

No worries