Fair warning
I do not have dba training or anything of the like but I am managing a rather large web app database. I know the basics but I'm not sure how to handle this latest scenario.
Setup
I have about 300 different databases. 1 common database that all clients share for pulling static info and the remaining are 1 for each individual client.
Scenario
I have a table of about 130k items that never change. Not the largest table in the system but still a fair size. I have about 300 copies of this table currently. One for each client. They are all identical except for 1 simple enable/disable flag. I also have a single database where I store common information for all clients such as templates and fixed db lists that do not contain the enable/disable ability.
Question
Is their a way to store this mostly common db table in my "community" database and still allow individual "client" databases to have the enable disable flag?
My Thoughts
I know I could just do a simple [id,enable] table in the client db but that would add a join to the system. Then the question is: does the effect of the join on 130K entries worth the saved space? I just feel like I'm missing something obvious with this whole scenario.
Best Answer
No answers for months so here is the solutions I've come up with on my own.
I don't know if this is the best but if anyone else has this question it's worth a thought.
The common table can be stored in the common db as desired. Then each local database (which will always use a subset of the 130K possible values) can either store just the enabled values in their local copy of the table. Or store just the IDs of the enabled items. A disable would effective be a delete and an enable would be a create of either an ID or of the entire row.