Posted by Bob Badour on 06/26/07 16:13

nyathancha@hotmail.com wrote:
> On Jun 26, 11:08 am, nyathan...@hotmail.com wrote:
>
>>On Jun 26, 10:29 am, "Tom Cooper"
>>
>>
>>
>><tomcoo...@comcast.no.spam.please.net> wrote:
>>
>>>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.com> wrote in message
>>
>>>news:1182755325.216207.318140@g37g2000prf.googlegroups.com...
>>
>>>>Hi,
>>
>>>>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?
>>
>>>>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.
>>
>>>>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.
>>
>>>>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!)
>>
>>>>Any advice, Information or resources are much appreciated.
>>
>>Thanks for the prompt replies everyone.
>>
>>>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.

[Back to original message]