I have a question regarding best practices in database design. In a
relational database, is it wise/necessary to sometimes create tables
that are not related to other tables through a foreign Key
relationship or does this always indicate some sort of underlying
design flaw. Something that requires a re evaluation of the problem
domain?
The reason I ask is because in our application, the user can perform x
number of high level operations (creating/updating projects, creating/
answering surveys etc. etc.). Different users can perform different
operations and each operation can manipulate one or more table. This
part of the system is done and working. Now there is a requirement to
have some sort of audit logging inside the database (separate from the
text based log file that the application generates anyway). This
"audit logging" table will contain high level events that occur inside
the application (which may or may not relate to a particular
operation). This table is in some sense related to every other table
in the database, as well as data that is not in the database itself
(exceptions, external events etc.). For example : it might have
entries that specify that at time x user created project y, at time A
user filled out survey B, at time C LDAP server was down, At time D an
unauthorized login attempt occurred etc.
As I said, these seems to suggest a stand alone, floating table with a
few fields that store entries regarding whats going on the system
without any direct relationship to other tables in the database. But I
just feel uneasy about creating such an isolated table. Another option
is to store the "logging" information in another schema/database, but
that doubles the maintainance work load. Not really looking forward to
maintaining/designing two different schemas.
I had a look at the microsoft adventureworks database schema diagram
and they seem to have 3 standalong tables - AWBuildVersion, ErrorLog
and DatabaseLog (unless i am reading it wrong!)
Any advice, Information or resources are much appreciated.On 25 Jun, 08:08, nyathan...@.hotmail.com wrote:
Quote:
Originally Posted by
Hi,
>
I have a question regarding best practices in database design. In a
relational database, is it wise/necessary to sometimes create tables
that are not related to other tables through a foreign Key
relationship or does this always indicate some sort of underlying
design flaw. Something that requires a re evaluation of the problem
domain?
>
The reason I ask is because in our application, the user can perform x
number of high level operations (creating/updating projects, creating/
answering surveys etc. etc.). Different users can perform different
operations and each operation can manipulate one or more table. This
part of the system is done and working. Now there is a requirement to
have some sort of audit logging inside the database (separate from the
text based log file that the application generates anyway). This
"audit logging" table will contain high level events that occur inside
the application (which may or may not relate to a particular
operation). This table is in some sense related to every other table
in the database, as well as data that is not in the database itself
(exceptions, external events etc.). For example : it might have
entries that specify that at time x user created project y, at time A
user filled out survey B, at time C LDAP server was down, At time D an
unauthorized login attempt occurred etc.
>
As I said, these seems to suggest a stand alone, floating table with a
few fields that store entries regarding whats going on the system
without any direct relationship to other tables in the database. But I
just feel uneasy about creating such an isolated table. Another option
is to store the "logging" information in another schema/database, but
that doubles the maintainance work load. Not really looking forward to
maintaining/designing two different schemas.
>
I had a look at the microsoft adventureworks database schema diagram
and they seem to have 3 standalong tables - AWBuildVersion, ErrorLog
and DatabaseLog (unless i am reading it wrong!)
>
Any advice, Information or resources are much appreciated.
Hi Nyathan,
In general terms it is quite acceptable to have a standalone table
with no FK relationships instansiated.
Indeed, in times gone by whole databases were created in this manner
as the overhead for OLTP with all the index data manipulation behind
the scences could bring a system to its knees. (PK & FK are backed by
'hidden' indexes).
As regards your specific task, (and here you'll realise I'm not from a
SQL Server background), check to see if there is an existing audit
ability in the rdbms.
Some rdbms allow for tracking all the activity on a table or all the
activity of a certain user, some allow both and some changed from one
to the other, (rats).
If you need to create your own system then think generic.
A single table with datetime stamp, username, table effected, action
taken, and the say 100 columns of varchar 100.
Each table you are seeking to audit will need triggers, ( insert,
update, delete), that calls a genric stored procedure that populates
the table you have created.
If your going to be doing this for a lot of tables or repeatedly I'd
advise writting a little noddy program to get the source table column
information from the systables and then generate the triggers.
The triggers will need to cause the stored procedure to write away
both the before and after image of the data.
BEWARE of bulk updates or deletes, ( best to disable the triggers
before you do them).
Also archiving / purging of your generic table becomes interesting,
depending on activity levels.
I would not recommend the above for busy tables.
Hope that helps, Tim|||<nyathancha@.hotmail.comwrote in message
news:1182755325.216207.318140@.g37g2000prf.googlegr oups.com...
Quote:
Originally Posted by
Hi,
>
I have a question regarding best practices in database design. In a
relational database, is it wise/necessary to sometimes create tables
that are not related to other tables through a foreign Key
relationship or does this always indicate some sort of underlying
design flaw. Something that requires a re evaluation of the problem
domain?
>
The reason I ask is because in our application, the user can perform x
number of high level operations (creating/updating projects, creating/
answering surveys etc. etc.). Different users can perform different
operations and each operation can manipulate one or more table. This
part of the system is done and working. Now there is a requirement to
have some sort of audit logging inside the database (separate from the
text based log file that the application generates anyway). This
"audit logging" table will contain high level events that occur inside
the application (which may or may not relate to a particular
operation). This table is in some sense related to every other table
in the database, as well as data that is not in the database itself
(exceptions, external events etc.). For example : it might have
entries that specify that at time x user created project y, at time A
user filled out survey B, at time C LDAP server was down, At time D an
unauthorized login attempt occurred etc.
>
As I said, these seems to suggest a stand alone, floating table with a
few fields that store entries regarding whats going on the system
without any direct relationship to other tables in the database. But I
just feel uneasy about creating such an isolated table. Another option
is to store the "logging" information in another schema/database, but
that doubles the maintainance work load. Not really looking forward to
maintaining/designing two different schemas.
>
I had a look at the microsoft adventureworks database schema diagram
and they seem to have 3 standalong tables - AWBuildVersion, ErrorLog
and DatabaseLog (unless i am reading it wrong!)
>
Any advice, Information or resources are much appreciated.
>
An isolated table that is not logically connected to the rest of the system
by foreign key references suggests something that, in concept, is really a
separate schema. Ordinarily the Universe of Discourse (the subject matter
that the data describes) is composed of items that are all related to each
other in one way or another. The schema that is derived from a model of the
Universe of discourse will likewise be interrelated.
The situation you describe may be just such a situation, a separate schema.
An audit logging table may be storing data not for what it says about the
"real world" subject matter that the rest of the data describes, but for
what it says about the series of events that the application processed.
If so, it could be legitimate design. Ultimately, the question boils down
to this: how do you intend to use the data captured in the logging table?
If it's going to be used in an isolated fashion (not combined with data in
other tables by the DBMS), then your design may well be legitimate.|||On Mon, 25 Jun 2007 00:08:45 -0700, nyathancha@.hotmail.com wrote:
Quote:
Originally Posted by
>Hi,
>
>I have a question regarding best practices in database design. In a
>relational database, is it wise/necessary to sometimes create tables
>that are not related to other tables through a foreign Key
>relationship or does this always indicate some sort of underlying
>design flaw. Something that requires a re evaluation of the problem
>domain?
Hi nyathancha,
It can happen, but it's definitely not common. For me, it would be a
reason to look again, but not to dismiss the design right away.
I have once encountered a situation where I needed unrelated tables.
This had to do with auditing, but not at all like the method you are
proposing - in fact, I don't really like what I think you're trying to
do. Having one table to log "everything" shares many of the problems of
the EAV design - you'll be creating a very generic table with a few very
generic columns. They can hold everything, making it virtually
impossible to constrain or query the data in the table. If this is the
kind of audit tale that should normally never be used but is only kept
for the 1 in a million chance of a completely unforeseen disaster, and
wasting countless man hours to sift manually through the collected data
is an acceptable price to pay in that situation, than this design MIGHT
be considered. In all other cases, I'd steer away from it and go for a
more constrained design.
The situation where I had to use unrelated tables was at a firm that had
to keep a full record of changes for some tables - so for each of those
tables, a history table was made with all the same columns, plus a
datetime (as part of the primary key), userid of who made the change,
etc. We then added triggers to the main tables to ensure that each
modification in those tables was properly recorded in the corresponding
history table. But we did NOT define any foreign keys, for the simple
reason that after e.g. a deletion of a customer, the change history of
that customer still had to be kept on file; we couldn't remove the
customer from the history table, and a foreign key to the customers
table would have prevented the DELETE.
Another example of a design with an unrelated table that I never used in
practice but can imagine easily enough, would be a single-row table to
hold an application's "processing date" (so that a batch that runs past
midnight can all be processed as if all was on the same date, and that
actions can be "redone" [or tested] on a simulated dy - I have worked
with such systems back in my mainframe PL/I programming days, but they
used flat files rather than databases <g>).
Quote:
Originally Posted by
>I had a look at the microsoft adventureworks database schema diagram
>and they seem to have 3 standalong tables - AWBuildVersion, ErrorLog
>and DatabaseLog (unless i am reading it wrong!)
For examples of good design, please don't look at any Microsoft supplied
samples. Even though AdventureWorks is miles ahead of Northwind and
pubs, it's still filled to the brim with bad practices.
--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis|||In addition to the other replies, I would add that foreign key constraints
are just one of many tools the database designer can use to help ensure that
bad data does not get placed in your database. Other tools include check
constraints, using the right datatypes (eg, store dates in a datetime
column, not a varchar column), sometimes triggers, etc.
So an important question is what the consequences will be if (when!, my
experience is if bad data can be put into a database, sooner or later, it
will be) invalid data is put into your audit table(s). That might range
from nobody really cares, to it's going to be a lot of work to fix it, to
somebody (you?) gets fired, to your company would be subject to a
significant fine, to somebody might go to prison (if, for example, your
audit trail is being used to prove compliance with SOX). So ask yourself
questions like what will happen if your boss comes to you and says the audit
trail says that user x created project y at time z, but there is no project
y in the system.
I certainly have tables in databases I have designed that do not have any
foreign key relationships to other tables, but before implementing one, I
would always think carefully about it.
Tom
<nyathancha@.hotmail.comwrote in message
news:1182755325.216207.318140@.g37g2000prf.googlegr oups.com...
Quote:
Originally Posted by
Hi,
>
I have a question regarding best practices in database design. In a
relational database, is it wise/necessary to sometimes create tables
that are not related to other tables through a foreign Key
relationship or does this always indicate some sort of underlying
design flaw. Something that requires a re evaluation of the problem
domain?
>
The reason I ask is because in our application, the user can perform x
number of high level operations (creating/updating projects, creating/
answering surveys etc. etc.). Different users can perform different
operations and each operation can manipulate one or more table. This
part of the system is done and working. Now there is a requirement to
have some sort of audit logging inside the database (separate from the
text based log file that the application generates anyway). This
"audit logging" table will contain high level events that occur inside
the application (which may or may not relate to a particular
operation). This table is in some sense related to every other table
in the database, as well as data that is not in the database itself
(exceptions, external events etc.). For example : it might have
entries that specify that at time x user created project y, at time A
user filled out survey B, at time C LDAP server was down, At time D an
unauthorized login attempt occurred etc.
>
As I said, these seems to suggest a stand alone, floating table with a
few fields that store entries regarding whats going on the system
without any direct relationship to other tables in the database. But I
just feel uneasy about creating such an isolated table. Another option
is to store the "logging" information in another schema/database, but
that doubles the maintainance work load. Not really looking forward to
maintaining/designing two different schemas.
>
I had a look at the microsoft adventureworks database schema diagram
and they seem to have 3 standalong tables - AWBuildVersion, ErrorLog
and DatabaseLog (unless i am reading it wrong!)
>
Any advice, Information or resources are much appreciated.
>
<tomcoo...@.comcast.no.spam.please.netwrote:
Quote:
Originally Posted by
In addition to the other replies, I would add that foreign key constraints
are just one of many tools thedatabasedesigner can use to help ensure that
bad data does not get placed in yourdatabase. Other tools include check
constraints, using the right datatypes (eg, store dates in a datetime
column, not a varchar column), sometimes triggers, etc.
>
So an important question is what the consequences will be if (when!, my
experience is if bad data can be put into adatabase, sooner or later, it
will be) invalid data is put into your audit table(s). That might range
from nobody really cares, to it's going to be a lot of work to fix it, to
somebody (you?) gets fired, to your company would be subject to a
significant fine, to somebody might go to prison (if, for example, your
audit trail is being used to prove compliance with SOX). So ask yourself
questions like what will happen if your boss comes to you and says the audit
trail says that user x created project y at time z, but there is no project
y in the system.
>
I certainly have tables in databases I have designed that do not have any
foreign key relationships to other tables, but before implementing one, I
would always think carefully about it.
>
Tom
>
<nyathan...@.hotmail.comwrote in message
>
news:1182755325.216207.318140@.g37g2000prf.googlegr oups.com...
>
Quote:
Originally Posted by
Hi,
>
Quote:
Originally Posted by
I have a question regarding best practices indatabasedesign. In a
relationaldatabase, is it wise/necessary to sometimes create tables
that are not related to other tables through a foreign Key
relationship or does this always indicate some sort of underlying
design flaw. Something that requires a re evaluation of the problem
domain?
>
Quote:
Originally Posted by
The reason I ask is because in our application, the user can perform x
number of high level operations (creating/updating projects, creating/
answering surveys etc. etc.). Different users can perform different
operations and each operation can manipulate one or more table. This
part of the system is done and working. Now there is a requirement to
have some sort of audit logging inside thedatabase(separate from the
text based log file that the application generates anyway). This
"audit logging" table will contain high level events that occur inside
the application (which may or may not relate to a particular
operation). This table is in some sense related to every other table
in thedatabase, as well as data that is not in thedatabaseitself
(exceptions, external events etc.). For example : it might have
entries that specify that at time x user created project y, at time A
user filled out survey B, at time C LDAP server was down, At time D an
unauthorized login attempt occurred etc.
>
Quote:
Originally Posted by
As I said, these seems to suggest a stand alone, floating table with a
few fields that store entries regarding whats going on the system
without any direct relationship to other tables in thedatabase. But I
just feel uneasy about creating such an isolated table. Another option
is to store the "logging" information in another schema/database, but
that doubles the maintainance work load. Not really looking forward to
maintaining/designing two different schemas.
>
Quote:
Originally Posted by
I had a look at the microsoft adventureworksdatabaseschema diagram
and they seem to have 3 standalong tables - AWBuildVersion, ErrorLog
and DatabaseLog (unless i am reading it wrong!)
>
Quote:
Originally Posted by
Any advice, Information or resources are much appreciated.
Thanks for the prompt replies everyone.
Quote:
Originally Posted by
>From what I am hearing, the consensus seems to be use it if you
absolutely must, but try to avoid it if you can.
One good point everyone seems to raise is "what is it used for?" ...
To be perfectly honest I am not entirely sure myself. Its one of those
requirements that filtered down from the management cloud. I think
the view is to use it mainly for "reporting" kind of functionality and
maybe only on some rare occasion for some sort of postmortem
debugging. Although in the latter situation, the application logs and
the sql server logs will probably end up being more helpful. I think
there is a system table somewhere in sql server that logs all the
transactions and changes that happen in the table right?
Crystal reports were being considered at some stage for more
sophisticated reports, but for now they want some sort of entries in
there to see whats happening (not necessarily at the database level,
but at the application level). The resolution of the reporting and
entries hasn't been decided yet ... as in, do we want to know
everytime someone retrieves a list of customers or only when someone
adds/removes customers. I have a feeling that if I chase this up, the
answer is going to be "both", "we may not want to start logging very
detailed stuff into the database right away, but if at some stage we
want to do it, the design should allow for it."
So just thinking in terms of some sort of "reporting" solution, in
abstract a sort of condensed data for easier consumption, does it make
sense to store an isolated table(s)/schemas along with the actual
data?
As to the consequences of a bad audit trail/log entry, I don't think
it would be catastrophic (fines, people going to prison etc.). Its an
internal application used to streamline inhouse processes. But of
course, we still don't want bad, inconsistent data in there and it
would lead to a lot of headaches, finger pointings, late nights etc.|||<nyathancha@.hotmail.comwrote in message
news:1182820095.745339.128220@.e9g2000prf.googlegro ups.com...
Quote:
Originally Posted by
One good point everyone seems to raise is "what is it used for?" ...
To be perfectly honest I am not entirely sure myself. Its one of those
requirements that filtered down from the management cloud. I think
the view is to use it mainly for "reporting" kind of functionality and
maybe only on some rare occasion for some sort of postmortem
debugging. Although in the latter situation, the application logs and
the sql server logs will probably end up being more helpful. I think
there is a system table somewhere in sql server that logs all the
transactions and changes that happen in the table right?
>
In architecture, form follows function. If the question "what is this data
being captured for" is unanswered, then the question of whether the design
is appropriate becomes moot.|||
<nyathancha@.hotmail.comwrote in message
news:1182820095.745339.128220@.e9g2000prf.googlegro ups.com...
Quote:
Originally Posted by
On Jun 26, 10:29 am, "Tom Cooper"
<tomcoo...@.comcast.no.spam.please.netwrote:
Quote:
Originally Posted by
>In addition to the other replies, I would add that foreign key
>constraints
>are just one of many tools thedatabasedesigner can use to help ensure
>that
>bad data does not get placed in yourdatabase. Other tools include check
>constraints, using the right datatypes (eg, store dates in a datetime
>column, not a varchar column), sometimes triggers, etc.
>>
>So an important question is what the consequences will be if (when!, my
>experience is if bad data can be put into adatabase, sooner or later, it
>will be) invalid data is put into your audit table(s). That might range
>from nobody really cares, to it's going to be a lot of work to fix it, to
>somebody (you?) gets fired, to your company would be subject to a
>significant fine, to somebody might go to prison (if, for example, your
>audit trail is being used to prove compliance with SOX). So ask yourself
>questions like what will happen if your boss comes to you and says the
>audit
>trail says that user x created project y at time z, but there is no
>project
>y in the system.
>>
>I certainly have tables in databases I have designed that do not have any
>foreign key relationships to other tables, but before implementing one, I
>would always think carefully about it.
>>
>Tom
>>
><nyathan...@.hotmail.comwrote in message
>>
>news:1182755325.216207.318140@.g37g2000prf.googlegr oups.com...
>>
Quote:
Originally Posted by
Hi,
>>
Quote:
Originally Posted by
I have a question regarding best practices indatabasedesign. In a
relationaldatabase, is it wise/necessary to sometimes create tables
that are not related to other tables through a foreign Key
relationship or does this always indicate some sort of underlying
design flaw. Something that requires a re evaluation of the problem
domain?
>>
Quote:
Originally Posted by
The reason I ask is because in our application, the user can perform x
number of high level operations (creating/updating projects, creating/
answering surveys etc. etc.). Different users can perform different
operations and each operation can manipulate one or more table. This
part of the system is done and working. Now there is a requirement to
have some sort of audit logging inside thedatabase(separate from the
text based log file that the application generates anyway). This
"audit logging" table will contain high level events that occur inside
the application (which may or may not relate to a particular
operation). This table is in some sense related to every other table
in thedatabase, as well as data that is not in thedatabaseitself
(exceptions, external events etc.). For example : it might have
entries that specify that at time x user created project y, at time A
user filled out survey B, at time C LDAP server was down, At time D an
unauthorized login attempt occurred etc.
>>
Quote:
Originally Posted by
As I said, these seems to suggest a stand alone, floating table with a
few fields that store entries regarding whats going on the system
without any direct relationship to other tables in thedatabase. But I
just feel uneasy about creating such an isolated table. Another option
is to store the "logging" information in another schema/database, but
that doubles the maintainance work load. Not really looking forward to
maintaining/designing two different schemas.
>>
Quote:
Originally Posted by
I had a look at the microsoft adventureworksdatabaseschema diagram
and they seem to have 3 standalong tables - AWBuildVersion, ErrorLog
and DatabaseLog (unless i am reading it wrong!)
>>
Quote:
Originally Posted by
Any advice, Information or resources are much appreciated.
>
Thanks for the prompt replies everyone.
>
Quote:
Originally Posted by
>>From what I am hearing, the consensus seems to be use it if you
absolutely must, but try to avoid it if you can.
>
One good point everyone seems to raise is "what is it used for?" ...
To be perfectly honest I am not entirely sure myself. Its one of those
requirements that filtered down from the management cloud. I think
the view is to use it mainly for "reporting" kind of functionality and
maybe only on some rare occasion for some sort of postmortem
debugging. Although in the latter situation, the application logs and
the sql server logs will probably end up being more helpful. I think
there is a system table somewhere in sql server that logs all the
transactions and changes that happen in the table right?
>
No. It doesn't. And depending on database options, the transaction log may
not contain a persistent record of changes either, not to mention that
special tools are required to read those logs.
It is sometimes important to determine who did what, when, where (from which
workstation), and how (with which application). It's easier to track down a
bug if you don't have to rely on what the user claims they did. It can also
be used to determine which users need additional training. A DBMS can only
guarantee that the result of a modification is consistent with respect to
the schema, it cannot determine whether or not a consistent modification is
also correct.
Quote:
Originally Posted by
Crystal reports were being considered at some stage for more
sophisticated reports, but for now they want some sort of entries in
there to see whats happening (not necessarily at the database level,
but at the application level). The resolution of the reporting and
entries hasn't been decided yet ... as in, do we want to know
everytime someone retrieves a list of customers or only when someone
adds/removes customers. I have a feeling that if I chase this up, the
answer is going to be "both", "we may not want to start logging very
detailed stuff into the database right away, but if at some stage we
want to do it, the design should allow for it."
>
So just thinking in terms of some sort of "reporting" solution, in
abstract a sort of condensed data for easier consumption, does it make
sense to store an isolated table(s)/schemas along with the actual
data?
>
I think it all depends on the requirements. Logging consumes resources and
therefore impacts all database operations.
Quote:
Originally Posted by
As to the consequences of a bad audit trail/log entry, I don't think
it would be catastrophic (fines, people going to prison etc.). Its an
internal application used to streamline inhouse processes. But of
course, we still don't want bad, inconsistent data in there and it
would lead to a lot of headaches, finger pointings, late nights etc.
>
Actually, you DO want bad information in there. Nobody's perfect, and it's
easier to find and correct mistakes if you have a record of how they got
into the database in the first place.
Quote:
Originally Posted by
>
>
Quote:
Originally Posted by
On Jun 26, 10:29 am, "Tom Cooper"
>
>
>
<tomcoo...@.comcast.no.spam.please.netwrote:
Quote:
Originally Posted by
In addition to the other replies, I would add that foreign key constraints
are just one of many tools thedatabasedesigner can use to help ensure that
bad data does not get placed in yourdatabase. Other tools include check
constraints, using the right datatypes (eg, store dates in a datetime
column, not a varchar column), sometimes triggers, etc.
>
Quote:
Originally Posted by
So an important question is what the consequences will be if (when!, my
experience is if bad data can be put into adatabase, sooner or later, it
will be) invalid data is put into your audit table(s). That might range
from nobody really cares, to it's going to be a lot of work to fix it, to
somebody (you?) gets fired, to your company would be subject to a
significant fine, to somebody might go to prison (if, for example, your
audit trail is being used to prove compliance with SOX). So ask yourself
questions like what will happen if your boss comes to you and says the audit
trail says that user x created project y at time z, but there is no project
y in the system.
>
Quote:
Originally Posted by
I certainly have tables in databases I have designed that do not have any
foreign key relationships to other tables, but before implementing one, I
would always think carefully about it.
>
Quote:
Originally Posted by
Tom
>
Quote:
Originally Posted by
<nyathan...@.hotmail.comwrote in message
>
Quote:
Originally Posted by
news:1182755325.216207.318140@.g37g2000prf.googlegr oups.com...
>
Quote:
Originally Posted by
Quote:
Originally Posted by
Hi,
>
Quote:
Originally Posted by
Quote:
Originally Posted by
I have a question regarding best practices indatabasedesign. In a
relationaldatabase, is it wise/necessary to sometimes create tables
that are not related to other tables through a foreign Key
relationship or does this always indicate some sort of underlying
design flaw. Something that requires a re evaluation of the problem
domain?
>
Quote:
Originally Posted by
Quote:
Originally Posted by
The reason I ask is because in our application, the user can perform x
number of high level operations (creating/updating projects, creating/
answering surveys etc. etc.). Different users can perform different
operations and each operation can manipulate one or more table. This
part of the system is done and working. Now there is a requirement to
have some sort of audit logging inside thedatabase(separate from the
text based log file that the application generates anyway). This
"audit logging" table will contain high level events that occur inside
the application (which may or may not relate to a particular
operation). This table is in some sense related to every other table
in thedatabase, as well as data that is not in thedatabaseitself
(exceptions, external events etc.). For example : it might have
entries that specify that at time x user created project y, at time A
user filled out survey B, at time C LDAP server was down, At time D an
unauthorized login attempt occurred etc.
>
Quote:
Originally Posted by
Quote:
Originally Posted by
As I said, these seems to suggest a stand alone, floating table with a
few fields that store entries regarding whats going on the system
without any direct relationship to other tables in thedatabase. But I
just feel uneasy about creating such an isolated table. Another option
is to store the "logging" information in another schema/database, but
that doubles the maintainance work load. Not really looking forward to
maintaining/designing two different schemas.
>
Quote:
Originally Posted by
Quote:
Originally Posted by
I had a look at the microsoft adventureworksdatabaseschema diagram
and they seem to have 3 standalong tables - AWBuildVersion, ErrorLog
and DatabaseLog (unless i am reading it wrong!)
>
Quote:
Originally Posted by
Quote:
Originally Posted by
Any advice, Information or resources are much appreciated.
>
Thanks for the prompt replies everyone.
>
Quote:
Originally Posted by
From what I am hearing, the consensus seems to be use it if you
>
absolutely must, but try to avoid it if you can.
>
One good point everyone seems to raise is "what is it used for?" ...
To be perfectly honest I am not entirely sure myself. Its one of those
requirements that filtered down from the management cloud. I think
the view is to use it mainly for "reporting" kind of functionality and
maybe only on some rare occasion for some sort of postmortem
debugging. Although in the latter situation, the application logs and
the sql server logs will probably end up being more helpful. I think
there is a system table somewhere in sql server that logs all the
transactions and changes that happen in the table right?
>
Crystal reports were being considered at some stage for more
sophisticated reports, but for now they want some sort of entries in
there to see whats happening (not necessarily at thedatabaselevel,
but at the application level). The resolution of the reporting and
entries hasn't been decided yet ... as in, do we want to know
everytime someone retrieves a list of customers or only when someone
adds/removes customers. I have a feeling that if I chase this up, the
answer is going to be "both", "we may not want to start logging very
detailed stuff into thedatabaseright away, but if at some stage we
want to do it, the design should allow for it."
>
So just thinking in terms of some sort of "reporting" solution, in
abstract a sort of condensed data for easier consumption, does it make
sense to store an isolated table(s)/schemas along with the actual
data?
>
As to the consequences of a bad audit trail/log entry, I don't think
it would be catastrophic (fines, people going to prison etc.). Its an
internal application used to streamline inhouse processes. But of
course, we still don't want bad, inconsistent data in there and it
would lead to a lot of headaches, finger pointings, late nights etc.
Actually, another best practices question now that I am here. Does it
make sense for a table to have two (or more different foreign keys)
both (or all) of which can be nullable and then tie them to different
tables for different records? For example I have a survey table. It
has all the fields are relations for describing various survey data
(survey questions, participants, start, finish dates etc. ). Now a
survey can be related to a project or a supplier. Of course, the same
thing can be done with two different junction tables. Which is the
better method? Add the junction tables and increasing the number of
tables, complexity of the system (and the number of joins required for
a query) or just adding extra nullable foreign key field(s) to the
table? Is there a rule of thumb I should be following here?|||<nyathancha@.hotmail.comwrote in message
news:1182830820.680395.9910@.e16g2000pri.googlegrou ps.com...
Quote:
Originally Posted by
On Jun 26, 11:08 am, nyathan...@.hotmail.com wrote:
Quote:
Originally Posted by
>On Jun 26, 10:29 am, "Tom Cooper"
>>
>>
>>
><tomcoo...@.comcast.no.spam.please.netwrote:
Quote:
Originally Posted by
In addition to the other replies, I would add that foreign key
constraints
are just one of many tools thedatabasedesigner can use to help ensure
that
bad data does not get placed in yourdatabase. Other tools include
check
constraints, using the right datatypes (eg, store dates in a datetime
column, not a varchar column), sometimes triggers, etc.
>>
Quote:
Originally Posted by
So an important question is what the consequences will be if (when!, my
experience is if bad data can be put into adatabase, sooner or later,
it
will be) invalid data is put into your audit table(s). That might
range
from nobody really cares, to it's going to be a lot of work to fix it,
to
somebody (you?) gets fired, to your company would be subject to a
significant fine, to somebody might go to prison (if, for example, your
audit trail is being used to prove compliance with SOX). So ask
yourself
questions like what will happen if your boss comes to you and says the
audit
trail says that user x created project y at time z, but there is no
project
y in the system.
>>
Quote:
Originally Posted by
I certainly have tables in databases I have designed that do not have
any
foreign key relationships to other tables, but before implementing one,
I
would always think carefully about it.
>>
Quote:
Originally Posted by
Tom
>>
Quote:
Originally Posted by
<nyathan...@.hotmail.comwrote in message
>>
Quote:
Originally Posted by
>news:1182755325.216207.318140@.g37g2000prf.googlegr oups.com...
>>
Quote:
Originally Posted by
Hi,
>>
Quote:
Originally Posted by
I have a question regarding best practices indatabasedesign. In a
relationaldatabase, is it wise/necessary to sometimes create tables
that are not related to other tables through a foreign Key
relationship or does this always indicate some sort of underlying
design flaw. Something that requires a re evaluation of the problem
domain?
>>
Quote:
Originally Posted by
The reason I ask is because in our application, the user can perform
x
number of high level operations (creating/updating projects,
creating/
answering surveys etc. etc.). Different users can perform different
operations and each operation can manipulate one or more table. This
part of the system is done and working. Now there is a requirement to
have some sort of audit logging inside thedatabase(separate from the
text based log file that the application generates anyway). This
"audit logging" table will contain high level events that occur
inside
the application (which may or may not relate to a particular
operation). This table is in some sense related to every other table
in thedatabase, as well as data that is not in thedatabaseitself
(exceptions, external events etc.). For example : it might have
entries that specify that at time x user created project y, at time A
user filled out survey B, at time C LDAP server was down, At time D
an
unauthorized login attempt occurred etc.
>>
Quote:
Originally Posted by
As I said, these seems to suggest a stand alone, floating table with
a
few fields that store entries regarding whats going on the system
without any direct relationship to other tables in thedatabase. But I
just feel uneasy about creating such an isolated table. Another
option
is to store the "logging" information in another schema/database, but
that doubles the maintainance work load. Not really looking forward
to
maintaining/designing two different schemas.
>>
Quote:
Originally Posted by
I had a look at the microsoft adventureworksdatabaseschema diagram
and they seem to have 3 standalong tables - AWBuildVersion, ErrorLog
and DatabaseLog (unless i am reading it wrong!)
>>
Quote:
Originally Posted by
Any advice, Information or resources are much appreciated.
>>
>Thanks for the prompt replies everyone.
>>
Quote:
Originally Posted by
>From what I am hearing, the consensus seems to be use it if you
>>
>absolutely must, but try to avoid it if you can.
>>
>One good point everyone seems to raise is "what is it used for?" ...
>To be perfectly honest I am not entirely sure myself. Its one of those
>requirements that filtered down from the management cloud. I think
>the view is to use it mainly for "reporting" kind of functionality and
>maybe only on some rare occasion for some sort of postmortem
>debugging. Although in the latter situation, the application logs and
>the sql server logs will probably end up being more helpful. I think
>there is a system table somewhere in sql server that logs all the
>transactions and changes that happen in the table right?
>>
>Crystal reports were being considered at some stage for more
>sophisticated reports, but for now they want some sort of entries in
>there to see whats happening (not necessarily at thedatabaselevel,
>but at the application level). The resolution of the reporting and
>entries hasn't been decided yet ... as in, do we want to know
>everytime someone retrieves a list of customers or only when someone
>adds/removes customers. I have a feeling that if I chase this up, the
>answer is going to be "both", "we may not want to start logging very
>detailed stuff into thedatabaseright away, but if at some stage we
>want to do it, the design should allow for it."
>>
>So just thinking in terms of some sort of "reporting" solution, in
>abstract a sort of condensed data for easier consumption, does it make
>sense to store an isolated table(s)/schemas along with the actual
>data?
>>
>As to the consequences of a bad audit trail/log entry, I don't think
>it would be catastrophic (fines, people going to prison etc.). Its an
>internal application used to streamline inhouse processes. But of
>course, we still don't want bad, inconsistent data in there and it
>would lead to a lot of headaches, finger pointings, late nights etc.
>
Actually, another best practices question now that I am here. Does it
make sense for a table to have two (or more different foreign keys)
both (or all) of which can be nullable and then tie them to different
tables for different records? For example I have a survey table. It
has all the fields are relations for describing various survey data
(survey questions, participants, start, finish dates etc. ). Now a
survey can be related to a project or a supplier. Of course, the same
thing can be done with two different junction tables. Which is the
better method? Add the junction tables and increasing the number of
tables, complexity of the system (and the number of joins required for
a query) or just adding extra nullable foreign key field(s) to the
table? Is there a rule of thumb I should be following here?
>
Yes. The presence of a null should only ever indicate that a value for an
*applicable* attribute is absent. If an attribute is not universally
applicable, then it should appear in a different relation schema. A
functional dependency A --B requires that whenever two different tuples
have the same set of values for A, they have the same set of values for B.
Just because one of the values in B has not been supplied doesn't alter that
requirement: whenever a value is supplied, it must necessarily be the same
in all tuples with the same set of values for A. Furthermore, if an
attribute is not universally applicable, then the dependency between A and B
is no longer functional, since even in a world in which all missing values
were supplied there can be some values for A that do not determine a value
for each element of B. Since a key implies a set of functional
dependencies, including attributes that do not universally apply is an
indication that a relation is not fully normalized because the relationship
between the key and the attribute is definitely not a functional dependency.
On the other hand, decomposing a relation where all attributes universally
apply but some may not be supplied introduces ambiguity: the sense that an
attribute universally applies is lost in translation. It cannot be
determined from the schema whether an attribute applies only some of the
time or if the attribute universally applies but a value hasn't yet been
supplied. So a null should only be used as a placeholder for a value that
has yet to be supplied.|||
Tim <tim_rogers01@.hotmail.comwrote:
Quote:
Originally Posted by
In general terms it is quite acceptable to have a standalone table
with no FK relationships instansiated.
Indeed, in times gone by whole databases were created in this manner
as the overhead for OLTP with all the index data manipulation behind
the scences could bring a system to its knees. (PK & FK are backed by
'hidden' indexes).
The only thing that will bring a system to its knees faster than
having indexes and FKs is *_not_* having indexes and FKs.
They are purely and simply a nightmare.
<auditing>
Quote:
Originally Posted by
I would not recommend the above for busy tables.
And there's a point to auditing tables that are relatively static -
normally lookups?
Paul...
Quote:
Originally Posted by
Hope that helps, Tim
--
plinehan __at__ yahoo __dot__ __com__
XP Pro, SP 2,
Oracle, 10.2.0.1 (Express Edition)
Interbase 6.0.2.0;
When asking database related questions, please give other posters
some clues, like operating system, version of db being used and DDL.
The exact text and/or number of error messages is useful (!= "it didn't work!").
Thanks.
Furthermore, as a courtesy to those who spend
time analysing and attempting to help, please
do not top post.|||nyathancha@.hotmail.com wrote:
Quote:
Originally Posted by
On Jun 26, 11:08 am, nyathan...@.hotmail.com wrote:
>
Quote:
Originally Posted by
>>On Jun 26, 10:29 am, "Tom Cooper"
>>
>>
>>
>><tomcoo...@.comcast.no.spam.please.netwrote:
>>
Quote:
Originally Posted by
>>>In addition to the other replies, I would add that foreign key constraints
>>>are just one of many tools thedatabasedesigner can use to help ensure that
>>>bad data does not get placed in yourdatabase. Other tools include check
>>>constraints, using the right datatypes (eg, store dates in a datetime
>>>column, not a varchar column), sometimes triggers, etc.
>>
Quote:
Originally Posted by
>>>So an important question is what the consequences will be if (when!, my
>>>experience is if bad data can be put into adatabase, sooner or later, it
>>>will be) invalid data is put into your audit table(s). That might range
>>>from nobody really cares, to it's going to be a lot of work to fix it, to
>>>somebody (you?) gets fired, to your company would be subject to a
>>>significant fine, to somebody might go to prison (if, for example, your
>>>audit trail is being used to prove compliance with SOX). So ask yourself
>>>questions like what will happen if your boss comes to you and says the audit
>>>trail says that user x created project y at time z, but there is no project
>>>y in the system.
>>
Quote:
Originally Posted by
>>>I certainly have tables in databases I have designed that do not have any
>>>foreign key relationships to other tables, but before implementing one, I
>>>would always think carefully about it.
>>
Quote:
Originally Posted by
>>>Tom
>>
Quote:
Originally Posted by
>>><nyathan...@.hotmail.comwrote in message
>>
Quote:
Originally Posted by
>>>news:1182755325.216207.318140@.g37g2000prf.googlegr oups.com...
>>
Quote:
Originally Posted by
>>Hi,
>>
Quote:
Originally Posted by
>>I have a question regarding best practices indatabasedesign. In a
>>relationaldatabase, is it wise/necessary to sometimes create tables
>>that are not related to other tables through a foreign Key
>>relationship or does this always indicate some sort of underlying
>>design flaw. Something that requires a re evaluation of the problem
>>domain?
>>
Quote:
Originally Posted by
>>The reason I ask is because in our application, the user can perform x
>>number of high level operations (creating/updating projects, creating/
>>answering surveys etc. etc.). Different users can perform different
>>operations and each operation can manipulate one or more table. This
>>part of the system is done and working. Now there is a requirement to
>>have some sort of audit logging inside thedatabase(separate from the
>>text based log file that the application generates anyway). This
>>"audit logging" table will contain high level events that occur inside
>>the application (which may or may not relate to a particular
>>operation). This table is in some sense related to every other table
>>in thedatabase, as well as data that is not in thedatabaseitself
>>(exceptions, external events etc.). For example : it might have
>>entries that specify that at time x user created project y, at time A
>>user filled out survey B, at time C LDAP server was down, At time D an
>>unauthorized login attempt occurred etc.
>>
Quote:
Originally Posted by
>>As I said, these seems to suggest a stand alone, floating table with a
>>few fields that store entries regarding whats going on the system
>>without any direct relationship to other tables in thedatabase. But I
>>just feel uneasy about creating such an isolated table. Another option
>>is to store the "logging" information in another schema/database, but
>>that doubles the maintainance work load. Not really looking forward to
>>maintaining/designing two different schemas.
>>
Quote:
Originally Posted by
>>I had a look at the microsoft adventureworksdatabaseschema diagram
>>and they seem to have 3 standalong tables - AWBuildVersion, ErrorLog
>>and DatabaseLog (unless i am reading it wrong!)
>>
Quote:
Originally Posted by
>>Any advice, Information or resources are much appreciated.
>>
>>Thanks for the prompt replies everyone.
>>
Quote:
Originally Posted by
>>>From what I am hearing, the consensus seems to be use it if you
>>
>>absolutely must, but try to avoid it if you can.
>>
>>One good point everyone seems to raise is "what is it used for?" ...
>>To be perfectly honest I am not entirely sure myself. Its one of those
>>requirements that filtered down from the management cloud. I think
>>the view is to use it mainly for "reporting" kind of functionality and
>>maybe only on some rare occasion for some sort of postmortem
>>debugging. Although in the latter situation, the application logs and
>>the sql server logs will probably end up being more helpful. I think
>>there is a system table somewhere in sql server that logs all the
>>transactions and changes that happen in the table right?
>>
>>Crystal reports were being considered at some stage for more
>>sophisticated reports, but for now they want some sort of entries in
>>there to see whats happening (not necessarily at thedatabaselevel,
>>but at the application level). The resolution of the reporting and
>>entries hasn't been decided yet ... as in, do we want to know
>>everytime someone retrieves a list of customers or only when someone
>>adds/removes customers. I have a feeling that if I chase this up, the
>>answer is going to be "both", "we may not want to start logging very
>>detailed stuff into thedatabaseright away, but if at some stage we
>>want to do it, the design should allow for it."
>>
>>So just thinking in terms of some sort of "reporting" solution, in
>>abstract a sort of condensed data for easier consumption, does it make
>>sense to store an isolated table(s)/schemas along with the actual
>>data?
>>
>>As to the consequences of a bad audit trail/log entry, I don't think
>>it would be catastrophic (fines, people going to prison etc.). Its an
>>internal application used to streamline inhouse processes. But of
>>course, we still don't want bad, inconsistent data in there and it
>>would lead to a lot of headaches, finger pointings, late nights etc.
>
Actually, another best practices question now that I am here. Does it
make sense for a table to have two (or more different foreign keys)
both (or all) of which can be nullable and then tie them to different
tables for different records? For example I have a survey table. It
has all the fields are relations for describing various survey data
(survey questions, participants, start, finish dates etc. ). Now a
survey can be related to a project or a supplier. Of course, the same
thing can be done with two different junction tables. Which is the
better method? Add the junction tables and increasing the number of
tables, complexity of the system (and the number of joins required for
a query) or just adding extra nullable foreign key field(s) to the
table? Is there a rule of thumb I should be following here?
With all due respect, performing work you lack the qualifications for by
using arbitrary answers from usenet amounts to malpractise. I strongly
urge you to learn the fundamentals BEFORE engineering solutions for anyone.|||On Mon, 25 Jun 2007 21:07:00 -0700, nyathancha@.hotmail.com wrote:
Quote:
Originally Posted by
>Actually, another best practices question now that I am here. Does it
>make sense for a table to have two (or more different foreign keys)
>both (or all) of which can be nullable and then tie them to different
>tables for different records?
Hi nyathancha,
This pattern isn't uncommon:
CREATE TABLE SomeTable
(SomePrimKey int NOT NULL,
FirstForeignKey int NULL,
SecondForeignKey int NULL,
-- Other columns,
PRIMARY KEY (SomePrimKey),
FOREIGN KEY (FirstForeignKey) REFERENCES SomeTable,
FOREIGN KEY (SecondForeignKey) REFERENCES OtherTable,
CHECK ((FirstForeignKey IS NULL AND SecondForeignKey IS NOT NULL)
OR (FirstForeignKey IS NOT NULL AND SecondForeignKey IS NULL))
);
--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
No comments:
Post a Comment