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 |
|
djfiii
Starting Member
13 Posts |
 Posted - 2008-11-22 : 17:33:47
|
| My objective is this: to create a structure that allows user defined fields AND historical data tracking WITHOUT having to modify table structures to accomodate these new fields. What I came up with so far is:ExceptionIDs (used exclusively for generating autoID)-exceptionID (PK, Identity)-exceptionEntryDate (Date)ExceptionFields (holds user defined fields)-exceptionFieldID (PK, Identity)-exceptionFieldLabel (varchar)-exceptionFieldDataType (varchar - limit user choices to text, number, date and will format programatically)ExceptionData (holds actual data - inserts only, never updates)-exceptionDataID (PK, Identity)-exceptionID (FK - ExceptionIDs table)-exceptionFieldID (FK - ExceptionFields table)-exceptionDataValue (text)-exceptionDataModifiedDate (date)-exceptionDataModifiedBy (FK - user table, not shown here)Let's say for example that I have 10 user defined fields, and 1 or more entries in the ExceptionData table for each of those fields. Let's say I pull back all records from the ExceptionData table, and it is 35 records. I'm trying to limit to the most recent entry for each of the 10 user defined fields. Additionally, if a field exists but has no data associated in the ExceptionData table, I still want to indicate that. I was playing with left outer joins on the ExceptionFields table but i can't seem to get what i need. Perhaps I'm not normalized fully the way I should be to accomplish this? Any help is much appreciated, and hopefully I have adequately described my situation. I can provide clarification if needed. Thanks!! |
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2008-11-22 : 21:53:24
|
quote:
Originally posted by djfiii
My objective is this: to create a structure that allows user defined fields AND historical data tracking WITHOUT having to modify table structures to accomodate these new fields.
...while your objective SHOULD be to understand your user requirements and the client's business model before you start coding.In the long run, that is the easier route to take....If it is not practically useful, then it is practically useless. |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-11-23 : 02:36:33
|
| didnt understand how you relate ExceptionFields to ExceptionIDs table for those who does have any data associated (no record in ExceptionData) |
|
|
|
djfiii
Starting Member
13 Posts |
Posted - 2008-11-24 : 13:18:36
|
quote:
Originally posted by blindman
quote:
Originally posted by djfiii
My objective is this: to create a structure that allows user defined fields AND historical data tracking WITHOUT having to modify table structures to accomodate these new fields.
...while your objective SHOULD be to understand your user requirements and the client's business model before you start coding.In the long run, that is the easier route to take....If it is not practically useful, then it is practically useless.
I was coming to that conclusion myself, but wanted to reach out to see if there was a common approach to this situation that I wasn't aware of. The approach instills some additional flexibility in structure, but seems to remove much of the flexibility for querying on the data. I am, unfortunately, in a position of dealing with constantly changing user requirements, but that is what it is. Thanks for the reply. |
|
|
|
djfiii
Starting Member
13 Posts |
Posted - 2008-11-24 : 13:22:29
|
quote:
Originally posted by visakh16
didnt understand how you relate ExceptionFields to ExceptionIDs table for those who does have any data associated (no record in ExceptionData)
My anticipation was a left outer join on the ExceptionFields table, such that wherever no data existed in the ExceptionData table for a particular field, the join would return a NULL for the data column. But it seems this approach is more complex than it's worth, so I'm going to abandon it. Thanks for reading. |
|
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2008-11-24 : 14:16:51
|
| The (too) common approaches are:1) EAV (Entity Attribute Value model)2) XML DatatypeIf I HAD to do this, I'd prefer xml, but recording an audit history may be easier with EAV.If it is not practically useful, then it is practically useless. |
|
|
|
|
|
|
|
|
Dynamic user defined fields