January 18, 2011 at 4:50 pm
I have a database where I'm tracking statistics on race horses (for example).
I want to maintain them in a STATISTICS table with columns: STAT_ID, STAT_NAME, STAT_DESCRIPTION, STAT_VALUE. I am tracking many kinds of statistics like height, weight, season, yards to full-speed, preferred jockey, but some require numeric datatypes, others require character datatypes. I am having to make this column varchar to accomodate all datatypes, but by doing so I preclude the possibility of running computations against this column.
Desired situation:
To be able to control the datatypes available for the STAT_VALUE column depending on the STAT_ID/STAT_NAME that it is being populated for. For example, if I happen to be entering a STATVALUE of "Mickey Mackerel" for STATNAME "Favored_Jockey", I would like the default constraints to be character datatype and possibly even a controlled vocabulary drop down list to be made available, and if I am entering a STATVALUE of "15" for STATNAME "yards_to_fullspeed", I would like the default datatype to be numeric only.
Is this the slightest bit possible?????! Thanks for reading. hxkresl
January 18, 2011 at 4:53 pm
hxkresl (1/18/2011)
To be able to control the datatypes available for the STAT_VALUE column depending on the STAT_ID/STAT_NAME that it is being populated for.Is this the slightest bit possible?????! Thanks for reading. hxkresl
To keep from putting too fine a point on it: Not in SQL Server, no. There are workarounds you can probably find, but the direct answer is the schema and optimizer won't behave well like this, so they don't let you do it.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
January 18, 2011 at 5:27 pm
Thank you Craig. Have I been assigned to your case? 😎
January 18, 2011 at 9:07 pm
hxkresl (1/18/2011)
Thank you Craig. Have I been assigned to your case? 😎
😀 No, I just happened to be around at that point. You're welcome. This isn't an uncommon problem but it IS considered bad design... mostly due to schema limitations. There is no pretty solution.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
January 18, 2011 at 11:15 pm
So, this problem in the data model, that I have described here, is the result of bad design, is what you're saying, right?
January 19, 2011 at 10:21 am
Correct. While it's intuitive to end users (and most DBAs), it causes no end of problems. For some examples, take a look around the net for "WHERE ISNUMERIC(<somefield>) does not work", and variations on that in google.
99% of the time, when you see that error, you'll find a schema trying to do exactly what you're doing, which is called (amongst other things) overloading. Overloading is a perfectly fine thing to do in C++. You never want to do it inside the DB Engine if you can help it. The optimizer cries. Like a little girl. Who lost her dolly.
If this data is merely for storage and will *never* be touched by the queries except for return to user, I might go with it. You can't enforce data type controls, can't enforce constraints properly, or any of the above, but I might for simplicity. It's always cost to design (schema work) vs. cost to implement (T-SQL Work) vs. cost to support (Hardware and data problem upkeep afterwards).
Increasing any of the above will usually lower the other two's necessity.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
January 19, 2011 at 10:33 am
For the record, this is not my :crazy: design, but I was trying to make what is, work. The actual solution still eludes me, as it will have so much impact all around.
Thank you Craig!
January 19, 2011 at 10:51 am
hxkresl (1/19/2011)
For the record, this is not my :crazy: design, but I was trying to make what is, work. The actual solution still eludes me, as it will have so much impact all around.Thank you Craig!
My pleasure. I'm sorry I can't really offer more than places for you to go research. To correct something like this long term usually requires some deep data analysis and data architecture. Much more than you will usually cover for free via a forum.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
January 19, 2011 at 10:52 am
hxkresl (1/18/2011)
Thank you Craig. Have I been assigned to your case? 😎
FYI, we're all volunteers helping out on this web site. As such, we pick-and-choose which posts to read, and help out on.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
January 19, 2011 at 11:13 am
I like that, thank you!!! I love this forum, the help, the fact you have never let me fall through the cracks. I am very happy about it. You are my friends 🙂
January 19, 2011 at 11:17 am
It is possible to design a table to do this kind of thing.
What you do is change it something like:
create table HorseSense (
ID int identity primary key,
HorseID int not null references dbo.Horses (ID) on delete cascade,
StatID int not null,
ValueType int not null,
constraint FK_Stats foreign key (StatID, ValueType) references dbo.Stats (ID, ValueType),
ValueNumeric float null,
ValueString varchar(max) null,
ValueDate datetime null,
ValueBinary bit null,
constraint OneValue check(ValueNumeric is not null and ValueString is null and ValueDate is null and ValueBinary is null
or ValueNumeric is null and ValueString is not null and ValueDate is null and ValueBinary is null
or ValueNumeric is null and ValueString is null and ValueDate is not null and ValueBinary is null
or ValueNumeric is null and ValueString is null and ValueDate is null and ValueBinary is not null),
constraint ValueTypeRight check (ValueType = 1 and ValueNumeric is not null
or ValueType = 2 and ValueString is not null
or ValueType = 3 and ValueDate is not null
or ValueType =4 and ValueBinary is not null));
Then you create a table of the stats you want to keep, including a type. The types match the column you want to use in this table. So, if "yards to full speed" is meant to be numeric, you'd have an entry in dbo.Stats with Name = "yards to full speed" and ValueType = 1. That, plus the constraints OneValue and ValueRightType will force that only numeric values can be stored for that stat.
You can also have a computed column that casts to SQLVariant datatype that uses either Coalesce or nested IsNull statements to return whichever value is filled in. (Simplifies querying overall data).
But, the simpler structure you have, no, it really doesn't support that kind of complex data control. Which is why it generally turns into a nightmare to query after only a short while in use. That table structure is derogatorily called a "one true lookup table", and every developer who doesn't really understand relational theory ends up wanting to build one at one point in time or another in their career or learning curve. So far, per all available data, they can be either very successful (it's never happened, but theoretically it could someday), really quite bad (pretty common), or a completely horrible nightmare that drives developers and DBAs to drink and heavy World of Warcraft addiction, with the usual complete loss of anything resembling "a life" that goes along with that (this only happens no more than 99% of the time, plus or minus 1% error rate).
- 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
January 19, 2011 at 8:14 pm
Dear GSquared. An answer that is a possible door in a seemingly impossible situation. I am thoughtfully consuming this info and thank you for tuning in.
January 21, 2011 at 6:21 am
You're welcome.
- 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
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply