Generic data types in SQL Server

Hi, I have a best question practice about database design. My idea is to
store business rules information for my customers. Basically, I define a se
t
of rules. For each rule, I know the type. Then I want to describe the
customers' sets of rules.
I want a table of CustomerID, RuleID, Value
Example:
Rule 1 = tax rate (Currency)
Rule 2 = Fiscal Year Start (Date)
Rule 3 = Have website (Boolean)
Then, my table might look like this: (CustomerID, RuleID, Value)
123,1,7.5
123,2,#1/1/5#
123,3,True
Obviously, I can't have a single column in SQL Server 2000 that holds
variant types. One solution is to have a column for each type and keep Null
values in all of them except the correct type column. That doesn't seem
right.
One suggestion was to cast the value to Binary, store it as such and then
cast the Binary value back to the expected data type when we need to use it.
Would that work in all cases?
Any thoughts are appreciated.John,
these are not 'business rules'...these are attributes of customer and
would most effectively be stored as separate attributes....overtyping
fields based on other fields is something we did in languages like
cobol to save space and coding.....not really something we should do
in an environment where the end user is supposed to query the data.
Best Regards
Peter Nolan
www.peternolan.com|||To add to Peter's reply, what you're doing is creating what is known as an
OTLT (One True Lookup Table) or EAV (Entity Attribute-Value). This is a
very common mistake that developers new to databases make, and it can cause
severe data integrity problems. Please refer to:
http://groups.google.com/groups?hl=...tlt&qt_s=Search
http://groups.google.com/groups?hl=...eav&qt_s=Search
http://groups.google.com/groups?hl=...orm&qt_s=Search
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"JohnMSyrasoft" <JohnMSyrasoft@.discussions.microsoft.com> wrote in message
news:6EFAB117-BD1F-48FF-B58F-C7DD61B13A86@.microsoft.com...
> Hi, I have a best question practice about database design. My idea is to
> store business rules information for my customers. Basically, I define a
> set
> of rules. For each rule, I know the type. Then I want to describe the
> customers' sets of rules.
> I want a table of CustomerID, RuleID, Value
> Example:
> Rule 1 = tax rate (Currency)
> Rule 2 = Fiscal Year Start (Date)
> Rule 3 = Have website (Boolean)
> Then, my table might look like this: (CustomerID, RuleID, Value)
> 123,1,7.5
> 123,2,#1/1/5#
> 123,3,True
> Obviously, I can't have a single column in SQL Server 2000 that holds
> variant types. One solution is to have a column for each type and keep
> Null
> values in all of them except the correct type column. That doesn't seem
> right.
> One suggestion was to cast the value to Binary, store it as such and then
> cast the Binary value back to the expected data type when we need to use
> it.
> Would that work in all cases?
> Any thoughts are appreciated.
>|||I looked at the first search and at the first thread that came up. OTLT was
advocated (amongst others) by a guy from HP Openview. I have worked with HP
Openview and I though it was a pile of junk*. I now understand why.
* clearest memory of that symptom: having a dialog box with 3 buttons, 2 of
which had the same keyboard shortcut
Jacco Schalkwijk
SQL Server MVP
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:eftQlTVzFHA.2076@.TK2MSFTNGP14.phx.gbl...
> To add to Peter's reply, what you're doing is creating what is known as an
> OTLT (One True Lookup Table) or EAV (Entity Attribute-Value). This is a
> very common mistake that developers new to databases make, and it can
> cause severe data integrity problems. Please refer to:
> http://groups.google.com/groups?hl=...tlt&qt_s=Search
> http://groups.google.com/groups?hl=...eav&qt_s=Search
> http://groups.google.com/groups?hl=...orm&qt_s=Search
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.datamanipulation.net
> --
>
> "JohnMSyrasoft" <JohnMSyrasoft@.discussions.microsoft.com> wrote in message
> news:6EFAB117-BD1F-48FF-B58F-C7DD61B13A86@.microsoft.com...
>|||Hi Adam,
wow, it even has a name? And I've not heard of it? That's something.
We have done things like encode large numbers of miscellaneous codes
into one lookup table to save ourselves the paid of having large
numbers of lookup tables...and one operational system I used to use at
IBM was the Common Table Management System where very large numbers of
code tables were stored....but these were not attributes of a defined
entity...or they were used to decode code stored on the original
entity when we wanted to save space....not an issue any more..
Peter