October 18, 2010 at 8:35 am
Here's my scenario. We run an application that can switch between database, each of the same schema, the only difference is that each database runs for a different company under its own set of rules. I keep the physical schema of each company in line so that when changes are made, new application modules are designed, etc., publishing them to all of the databases is simple. Occasionally I do hit areas where the same stored procedure may operate differently for one company to another, but i have a method in place to handle these.
However I have noticed more and more that where procedures do differ, it is due to different lookup id codes, fee values, vat rates, etc. I want to implement a kind of "configuration" table that procedures can use by using a key to find a value; the developers have also expressed an interest in such a table and one per database as opposed to one per application is their desired solution.
As far as I can see, there are pretty much three solutions to this that i have seen implemented.
The first is a simple Key/Value pair. On the positive side the table is simple, but the values have to be stored as text and then cast to the relevant data type either by the procedure or application.
The second is a "single-row" table with each field being a setting. This has the advantage of strong-typing, but requires a schema change every time a new option is added (and a resulting very long row).
The third option is to have a single row per setting, but rather than having a VARCHAR field holding the value, have one field for each major data type (smallint and tinyint can be stored in an int field for example - we rarely have a need to hold big int values), a further field can then indicate the field type that is to be used for the setting (as kind of a belt and braces for anyone simply looking at the raw data with an eye to change a setting).
I'm curious as to what people out there think, and if there are any other solutions to this problem that people have devised. Whatever the solution is, it needs to be simple and easy to handle (pretty much ruling out XML data types). Complex data types such as XML, binary, spatial, etc., are not required.
---------------------------------------
It is by caffeine alone I set my mind in motion.
It is by the Beans of Java that thoughts acquire speed,
the hands acquire shaking, the shaking becomes a warning.
It is by caffeine alone I set my mind in motion.
October 18, 2010 at 10:54 am
Key/Value is how I've done that.
Using the SQLVariant datatype might help, or might not, but is worth looking at. But really, converting a few values from varchar to int or numeric or whatever is no big deal. It only really matters if you have to do it a LOT, and then having it stored in separate columns for data types works.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 29, 2010 at 11:59 pm
I think that option one is by far your best choice.
For option 2, I don't think you'd want to change the schema every time you want to add a configuration value.
For option 3, I don't see the benefit of maintaining several columns to hold just one value. A more normalized way to do it would probably be to store the value, as well as the data type. That said, I don't see much benefit of even storing the datatype unless you envision using configuration values without even knowing what they were being used for (perhaps through generated code?)
In addition to name/value pairs, consider adding a field for ApplicationName or some other identifier/namespacer if you foresee the need to have similar/same named values for different applications. Also consider the standard DeltaUser, DeltaTS, CreatedUser, CreatedTs if it may be important to see when configuration values changed, and who changed them.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply