Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
ch9862
Yak Posting Veteran
76 Posts |
 Posted - 2011-02-09 : 20:50:46
|
| I'm looking for ideas - perhaps somebody had similar problem in the past and could offer some pointers.There is an old application, dealing with known entities. Every now and then, the users want to add more data to be gathered, which means somebody has to update the database as well as the app. Since these changes take time, the users asked for "flexibility" to be built into the app. They want to be able to design ("script") new "data set" without having to make changes to the database structure or the application code - only the data.I began researching it, and I can add definition for a "constant" dataset. Think of it as a "scripted" dialog box - when it loads, it reads from the database what controls should it display, and what data to put in them. I can link it to an existing database entity, and everything's peachy. But the problem arises when some of this data has more dimensions.For example, imagine that the users asks to store some data about each employee of an institution. Not a problem - our main entity (institution) is known from the get-go. I don't mean actual institution record ID, but rather which table / field stores it.The user adds a bunch of records to the metadata tables, describing each data element they want to track. They want to capture Last name (text box, string), first name (text box, string), age (text box, integer), education (dropdown list, set range of values), date of birth (text box, date). I can imagine how to deisgn tables for both the metadata and the actual data.But the problem appears when the user wants to define a "tracking system" that: keeps all this info stated above for each employee, plus some date-related data. For example eployee's salary every year (or month, or quarter). Now some of these "data elements" about each employee gain an additional dimension.Even worse, the user might want to say: I want to track not just employee data, including their salary each Jan 1, but also want to group it by branch office. So first we see the list of branch offices, and upon selecting a particular one, we see the list of employees, each with her/his own salary history.This is not a big deal when I can re-design database (and update the application code) every time the user tells me what else they want to track. But how do I solve the "how-many-dimensions-do-I-have" problem, when the user wants the whole thing to be database driven?Any ideas where I should look? Books, online resources?Any and all suggestions appreciated. |
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2011-02-10 : 04:20:27
|
| There is a concept called the "EAV-model" (http://en.wikipedia.org/wiki/Entity-attribute-value_model) that more or less handles parts of your situation with the scripted dialogs and such. This model is extremely flexible in terms of entering data in to the database, but to get it back out and make relations between this data is a whole different thing. What you're basically asking is how you can build a dynamic relational database inside your relational database and it can be done but it's a HUGE can of worms. Call any SAP developer/DBA and they'll tell you all about it. My advice would be to maybe use the EAV model for the simple stuff, but do it the old fashioned way with the more complex stuff where you have relations and constraints and everything.- LumbagoMy blog-> www.thefirstsql.com |
 |
|
|
ch9862
Yak Posting Veteran
76 Posts |
Posted - 2011-03-04 : 22:11:00
|
quote:
Originally posted by Lumbago
There is a concept called the "EAV-model" (...)
Thank you! Sorry I did not even respond in a timely fashion - I still appreciate the suggestion.So far we're moving along these lines, although we were able to get the user to agree to some limitations (esp. number of dimensions) - so although the tables we're using are "long and skinny", things are manageable.We're storing some of the metadata as XML, because the user agreed this doesn't have to be "searchable", which also limits complexity of the database.Thank you again for the link! |
|
|
|
|
|
|
|
|
How to title this...