Mysql – database table design to avoid storing table names in a table

database-designMySQLnormalization

So my scenario demands to store details of businesses which offer varied services. One business can offer n different services. Since each service is different, details pertaining to a service are in individual tables. 

table : business 
------------------
business_id, 
(other business specific fields)


table: service_types
----------------------
service_type_id,
service_name,
(other service specific fields)


table: business_services
--------------------------
service_type_id_fk,
business_id_fk,
identifier (id to point the record in respective service table)
(other business service specific fields)


table: service_a (similarly many other services - service_b, service_c etc)
------------------
service_id,
service_type_id_fk,
(other service specific fields)

Now I need to find out

  1. How many services a business provides
  2. Details of each of the service.

For 1, I can simply look up business_services table.

But for 2, with the design I have, I have to store the tables names somewhere; either in server side scripting or in a dedicated table. I can may be store it in service_types table where I store the service name and its table name for further details.

Is it good practice to store table names in a table?
Or what could be the best way to address the problem?

NOTE:
None of these services are similar and they demand a table for each one. So grouping services into one table is not an option because I want it to be normalized and distribute load as well.

Best Answer

If you know in advance that you're going to have at most a smallish (under 10) set of services and you don't need to add/remove/change them programmatically or often, I would use separate link tables for the services. Since the client needs to request a service to be added, it's probably a lot simpler to create a new link table and make code changes to reflect the new service than to create a "clever solution" which is overly clever for the amount of changes you're going to have.

With generic solutions you tend to exchange efficiency/simplicity and other things for ease of use (e.g. you have just a single generic link table, but you end up with more complexity because you can't have a foreign key, your queries need to work with your custom solution, you must differentiate the services somehow in your application anyway, etc.).

Even if you were to have a lot of services and they would often change, I wouldn't go for the "dynamic link table" approach. You can definitely make it work somehow, but it would be a hack rather than a good solution. So what would be a good solution in that case? Hard to say from the question alone, but I'm sure it would involve a lot more than just the database.

The gist of the question is "How can I treat different things as if they were the same, except in many cases they need special treatment", and I'm offering the "don't even try if it isn't strictly needed" answer. If that's not to your liking, you'll have to explain a lot more about your system, the services, the data, etc.

One (not saying the best) way to get the amount of services provided by a business would be to use UNION for example. It gives a readable (and easily editable!) query, for when a service is added.

SELECT COUNT(*) FROM (
SELECT 1 FROM business bus JOIN business_services_a a ON (bus.id = a.business_id)
UNION
SELECT 1 FROM business bus JOIN business_services_b b ON (bus.id = b.business_id)
UNION
SELECT 1 FROM business bus JOIN business_services_c c ON (bus.id = b.business_id)
)
Related Question