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 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

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

generic audit trail best practice questions

hello everyone,
so i have the need for an audit trail for certain activities. ideally i
would like a generic audit trail table, that denotes the kind of
activity, the entity reference it was performed on, and the entity
reference it was performed by.
for example, there are currently entities like so (greatly simplified):
create table user (
userid uniqueidentifier primary key not null,
name varchar(50),
email varchar(50))
create table employee (
employeeid uniqueidentifier primary key not null,
name varchar(50),
role varchar(50))
create table businessthingy (
thingyid uniqueidentifier primary key not null,
description varchar(50),
modifiedby uniqueidentifier not null)
create table otherbusinessthingy (
otherthingyid uniqueidentifier primary key not null,
quantity int)
create table audit (
auditid uniqueidentifier primary key not null,
activity varchar(50),
performedby uniqueidentifier not null,
performedon uniqueidentifier not null)
so that's a rough sketch. some activities are frequent enough, and
unimportant enough that just capturing rowlevel audits is fine, which
is why there is a 'modifiedby' in the businessthingy table. we don't
want or need to keep track of a full history of edits, just knowing the
last person to edit something is good enough.
the audit table will capture more important (and less frequent)
activities though, such as inserts and deletes.
so my first question is: is it even possible to impose referential
integrity in this scheme? for example, the businessthingy can be
modified by both users and employees. also, the audit event can be
performed by both users and employees, and they can be performed on
both businesthingies and otherbusinessthingies. i can freely insert the
id's in appropriately, but can i enforce referential integrity across
multiple possible tables?
my second question is: are there better practices for this kind of
thing in order to ensure referential integrity? or is this a typical
hurdle for generic activity auditing?
thanks for any help / advice,
jasonjason (iaesun@.yahoo.com) writes:
> so my first question is: is it even possible to impose referential
> integrity in this scheme? for example, the businessthingy can be
> modified by both users and employees. also, the audit event can be
> performed by both users and employees, and they can be performed on
> both businesthingies and otherbusinessthingies. i can freely insert the
> id's in appropriately, but can i enforce referential integrity across
> multiple possible tables?
I don't think you should have referential integrity for users. Those should
be readable ids or names. Think sa and Query Analyzer. That's also stuff
you need to audit.
References for the audited entities is another matter. Here it's more
relevant to referential integrity. I think we do this in most of our
auditing. Important thing if you don't you need to store something that
users can read later. Storing a GUID which means nothing is not a good
thing.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||I've used trigger / audit table based auditing in the past, but have found
it to be cumbersome to program and brittle, especially in systems that are
complex or in a constant state of development. There were occasions where
trigger caused transactions to fail, becuase of flaws in the audit
programming or when the data model changed.
Instead, consider using SQL Server Profiler for auditing:
http://msdn.microsoft.com/library/d...
ethowto15.asp
Unlike triggers, it requires no programming and, it can be configured to
audit most any event at a ganular level: including table updates, table
selects, execs; even deadlocks, locking. The output can be directed to a SQL
Server table, and a scheduled job can query for specific red flag events and
send notifications. You can get it up and running in a single afternoon, and
it's simple to enable / disable events on specific objects or users, if
needed. Also, it can be used for analyzing system performance.
"jason" <iaesun@.yahoo.com> wrote in message
news:1137171394.413876.147000@.z14g2000cwz.googlegroups.com...
> hello everyone,
> so i have the need for an audit trail for certain activities. ideally i
> would like a generic audit trail table, that denotes the kind of
> activity, the entity reference it was performed on, and the entity
> reference it was performed by.
> for example, there are currently entities like so (greatly simplified):
> create table user (
> userid uniqueidentifier primary key not null,
> name varchar(50),
> email varchar(50))
> create table employee (
> employeeid uniqueidentifier primary key not null,
> name varchar(50),
> role varchar(50))
> create table businessthingy (
> thingyid uniqueidentifier primary key not null,
> description varchar(50),
> modifiedby uniqueidentifier not null)
> create table otherbusinessthingy (
> otherthingyid uniqueidentifier primary key not null,
> quantity int)
> create table audit (
> auditid uniqueidentifier primary key not null,
> activity varchar(50),
> performedby uniqueidentifier not null,
> performedon uniqueidentifier not null)
> so that's a rough sketch. some activities are frequent enough, and
> unimportant enough that just capturing rowlevel audits is fine, which
> is why there is a 'modifiedby' in the businessthingy table. we don't
> want or need to keep track of a full history of edits, just knowing the
> last person to edit something is good enough.
> the audit table will capture more important (and less frequent)
> activities though, such as inserts and deletes.
> so my first question is: is it even possible to impose referential
> integrity in this scheme? for example, the businessthingy can be
> modified by both users and employees. also, the audit event can be
> performed by both users and employees, and they can be performed on
> both businesthingies and otherbusinessthingies. i can freely insert the
> id's in appropriately, but can i enforce referential integrity across
> multiple possible tables?
> my second question is: are there better practices for this kind of
> thing in order to ensure referential integrity? or is this a typical
> hurdle for generic activity auditing?
> thanks for any help / advice,
> jason
>|||we have deliberately chosen guid's for all of our entity relational
keys for a variety of reasons, so wherever we are referring to another
singular entity, guid is how we want to do it. if we want to pull user
digestible information, then we do that as reporting functionality
through the foreign relationship of entities.
so i'm curious, how do you enforce referential integrity for diverse
audited entities?|||interesting. i'm not sure this would have the 'user' level that we
would need though. you see users of the application do not map to
database users. we use the application profile for sql identification,
and rely on data content to define the state of user entities (ergo the
user table). could we, in that setup, use profiler to capture data at a
user level, when the user entities are not database users, but
application users stored in a table?|||When the application performs an operation on the database, do you call a
stored procedure with the user's id as a parameter like so?
exec usp_EditInvoice @.InvoiceID=3607, @.UserID=8
"jason" <iaesun@.yahoo.com> wrote in message
news:1137182806.078657.32390@.f14g2000cwb.googlegroups.com...
> interesting. i'm not sure this would have the 'user' level that we
> would need though. you see users of the application do not map to
> database users. we use the application profile for sql identification,
> and rely on data content to define the state of user entities (ergo the
> user table). could we, in that setup, use profiler to capture data at a
> user level, when the user entities are not database users, but
> application users stored in a table?
>|||jason (iaesun@.yahoo.com) writes:
> we have deliberately chosen guid's for all of our entity relational
> keys for a variety of reasons, so wherever we are referring to another
> singular entity, guid is how we want to do it. if we want to pull user
> digestible information, then we do that as reporting functionality
> through the foreign relationship of entities.
OK. But keep in mind what said about the user names.

> so i'm curious, how do you enforce referential integrity for diverse
> audited entities?
So, I lied a bit. :-)
Some of our autiding stuff are specific to a process, so there are no
generic columns, but all columns have a unique meaning. Ref. integrity is
trivial.
Then we have at least one generic log table where the keys are
"tablename", "keyvalue1", "keyvalue2", and "columnname". This table
has no referential integrity.
For SQL 2005 our idea is to improve our auditing (which is a bit
substandard), by using the xml datatype, and simply log an after-
image of the row with XML. A generic client could then easily show
the user only differences between two updates.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||yes, something quite like that. we call it an 'authorityid' but
basically equates to either a userid or an employeeid, depending on who
is interacting with the entity|||interesting. is there any concern whether the XML after-image affects
the throughput of each transaction significantly?
thanks very much for this information. your practices make a lot of
sense, in terms of which audits can and cannot have referential
integrity. very helpful!
jason|||SQL Server Profiler can trace the command text of stored procedure calls,
including what parameters were passed. This will tell you what was executed,
when, and by whom. It can also trace other event attributes such as the
duration of the execution and application name. For example:
2005/01/16 10:31am exec usp_EditInvoice @.InvoiceID=3607, @.AuthroityID=8
However, using seperate SQL Server logins for each user (especially Windows
Authenitcated accounts) makes for easier and more robust auditing.
"jason" <iaesun@.yahoo.com> wrote in message
news:1137421941.558008.58370@.z14g2000cwz.googlegroups.com...
> yes, something quite like that. we call it an 'authorityid' but
> basically equates to either a userid or an employeeid, depending on who
> is interacting with the entity
>

General: Audit Dimension

What is the best practice for setting up audit dimensions in 2005? I know that we have the audit transform available, but should we be capturing extra information beyond this?

I had seen one of the design tip articles by kimball on what should be added, however, this article was from some time ago... (http://kimballgroup.com/html/designtipsPDF/DesignTips2001/KimballDT26AddingAnAudit.pdf)

Also, I try to capture the audit guid in the record and then, later, capture the rest of the audit information... I notice that the guid's are different between the audit transforms... is there a way to capture all of the information but not write it all out to each record? i.e. only capture the guid to the record and the rest of the information to the audit table.

Take a look at the Kimball Webcast on the first page of this forum:. Joy talks about creating auditing features:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=534505&SiteID=1|||

Thanks phil. I have watched that video in the past, I might take another look at it. I was, however, hoping for a bit of discussion on what others in the industry were doing. I have looked through the Microsoft Data Warehouse Toolkit, Practical B.I. for SQL Server 2005, Delivering B.I. with Microsoft 2005, as well as a few of the articles on the web. I was just curious, since each has a different level of detail which they capture, what is acceptable down the road (I was told at one of the tdwi conferences that when building the warehouse, the first thing most people would do if they had the oppurtunity to start over is set up a good mdm / audit system, so that is what I'm trying to do)...

|||I implement a version of what Joy illustrated in her Webcast.