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.
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=e...lt&qt_s=Search
http://groups.google.com/groups?hl=e...av&qt_s=Search
http://groups.google.com/groups?hl=e...rm&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=e...lt&qt_s=Search
> http://groups.google.com/groups?hl=e...av&qt_s=Search
> http://groups.google.com/groups?hl=e...rm&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
Showing posts with label define. Show all posts
Showing posts with label define. Show all posts
Monday, March 26, 2012
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
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
Wednesday, March 21, 2012
Generating reports dynamically
I am new to reporting and Yukon and trying to understand reporting services.
I am trying
to determine if it is possible to define reports programmatically and send
them to a yukon server (or SQL server 2000) and have it generate an Excel
report and place it at the specified location on the file server. What
complicates this picture is that the reports to be generated/queries to be
performed are very complex. Can such complex queries be defined dynamically
and sent to the server via an API call ... can we use a stored procedure, if
necessary, to return the data?
I looked briefly at RDL and the web services but could not determine if they
could be leveraged for our solution. Any input you have will be greatly
appreciated.Yes, you can generate reports dynamically via the API... you can also run
them,,, you can create subscriptions to run immediately, and send the report
to a file share..
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"IP" wrote:
> I am new to reporting and Yukon and trying to understand reporting services.
> I am trying
> to determine if it is possible to define reports programmatically and send
> them to a yukon server (or SQL server 2000) and have it generate an Excel
> report and place it at the specified location on the file server. What
> complicates this picture is that the reports to be generated/queries to be
> performed are very complex. Can such complex queries be defined dynamically
> and sent to the server via an API call ... can we use a stored procedure, if
> necessary, to return the data?
> I looked briefly at RDL and the web services but could not determine if they
> could be leveraged for our solution. Any input you have will be greatly
> appreciated.|||Thank you Wayne. Can you please point me to which API can be used for this
purpose and any websites that I can go to for more information on this.
Thanks in advance,
IP
"Wayne Snyder" wrote:
> Yes, you can generate reports dynamically via the API... you can also run
> them,,, you can create subscriptions to run immediately, and send the report
> to a file share..
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> I support the Professional Association for SQL Server ( PASS) and it''s
> community of SQL Professionals.
>
> "IP" wrote:
> > I am new to reporting and Yukon and trying to understand reporting services.
> > I am trying
> > to determine if it is possible to define reports programmatically and send
> > them to a yukon server (or SQL server 2000) and have it generate an Excel
> > report and place it at the specified location on the file server. What
> > complicates this picture is that the reports to be generated/queries to be
> > performed are very complex. Can such complex queries be defined dynamically
> > and sent to the server via an API call ... can we use a stored procedure, if
> > necessary, to return the data?
> >
> > I looked briefly at RDL and the web services but could not determine if they
> > could be leveraged for our solution. Any input you have will be greatly
> > appreciated.
I am trying
to determine if it is possible to define reports programmatically and send
them to a yukon server (or SQL server 2000) and have it generate an Excel
report and place it at the specified location on the file server. What
complicates this picture is that the reports to be generated/queries to be
performed are very complex. Can such complex queries be defined dynamically
and sent to the server via an API call ... can we use a stored procedure, if
necessary, to return the data?
I looked briefly at RDL and the web services but could not determine if they
could be leveraged for our solution. Any input you have will be greatly
appreciated.Yes, you can generate reports dynamically via the API... you can also run
them,,, you can create subscriptions to run immediately, and send the report
to a file share..
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"IP" wrote:
> I am new to reporting and Yukon and trying to understand reporting services.
> I am trying
> to determine if it is possible to define reports programmatically and send
> them to a yukon server (or SQL server 2000) and have it generate an Excel
> report and place it at the specified location on the file server. What
> complicates this picture is that the reports to be generated/queries to be
> performed are very complex. Can such complex queries be defined dynamically
> and sent to the server via an API call ... can we use a stored procedure, if
> necessary, to return the data?
> I looked briefly at RDL and the web services but could not determine if they
> could be leveraged for our solution. Any input you have will be greatly
> appreciated.|||Thank you Wayne. Can you please point me to which API can be used for this
purpose and any websites that I can go to for more information on this.
Thanks in advance,
IP
"Wayne Snyder" wrote:
> Yes, you can generate reports dynamically via the API... you can also run
> them,,, you can create subscriptions to run immediately, and send the report
> to a file share..
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> I support the Professional Association for SQL Server ( PASS) and it''s
> community of SQL Professionals.
>
> "IP" wrote:
> > I am new to reporting and Yukon and trying to understand reporting services.
> > I am trying
> > to determine if it is possible to define reports programmatically and send
> > them to a yukon server (or SQL server 2000) and have it generate an Excel
> > report and place it at the specified location on the file server. What
> > complicates this picture is that the reports to be generated/queries to be
> > performed are very complex. Can such complex queries be defined dynamically
> > and sent to the server via an API call ... can we use a stored procedure, if
> > necessary, to return the data?
> >
> > I looked briefly at RDL and the web services but could not determine if they
> > could be leveraged for our solution. Any input you have will be greatly
> > appreciated.
Subscribe to:
Posts (Atom)