Database design question - Isolated, unrelated tables

#11

<nyathancha (AT) hotmail (DOT) com> wrote

Quote:

On Jun 26, 11:08 am, nyathan... (AT) hotmail (DOT) com wrote:
On Jun 26, 10:29 am, "Tom Cooper"

tomcoo... (AT) comcast (DOT) 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... (AT) hotmail (DOT) com> wrote in message

news:1182755325.216207.318140 (AT) g37g2000prf (DOT) 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?

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.

#12

Tim <tim_rogers01 (AT) hotmail (DOT) com> wrote:

Quote:

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:

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:

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.

#13

nyathancha (AT) hotmail (DOT) com wrote:

Quote:

On Jun 26, 11:08 am, nyathan... (AT) hotmail (DOT) com wrote:

On Jun 26, 10:29 am, "Tom Cooper"

tomcoo... (AT) comcast (DOT) 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... (AT) hotmail (DOT) com> wrote in message

news:1182755325.216207.318140 (AT) g37g2000prf (DOT) 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.

#14

On Mon, 25 Jun 2007 18:08:15 -0700, nyathancha (AT) hotmail (DOT) com wrote:

(snip)

Quote:

I think
there is a system table somewhere in sql server that logs all the
transactions and changes that happen in the table right?

Hi nyathancha,

Wrong. SQL Server has a log file that it uses internally for rolling
transactions back, or rolling forward after an unexpected shutdown. The
format is not documented, though there are some third-party tool vendors
that claim their tools can read it. Even more important, the log file
can be cleaned for various reasons. This file isn't intended to be used
for auditing at all.

(snip)

Quote:

The resolution of the reporting and
entries hasn't been decided yet

But you (or rahter, "they") do want SOME form of reporting on the data,
right? In that case, you need to model it appropriately - find out what
attributes need to be stored, normalize down to a proper table design,
then implement.

Any idea of generating reports on a generic "catch-all" table design
such as what you're proposing will turn into a nightmare.

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

#15

On Mon, 25 Jun 2007 21:07:00 -0700, nyathancha (AT) hotmail (DOT) com wrote:

Quote:

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

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Database design question - Isolated, unrelated tables

comp.databases.ms-sqlserver comp.databases.ms-sqlserver

Re: Database design question - Isolated, unrelated tables - 06-26-2007 , 08:59 AM

Re: Database design question - Isolated, unrelated tables - 06-26-2007 , 10:44 AM

Re: Database design question - Isolated, unrelated tables - 06-26-2007 , 11:13 AM

Re: Database design question - Isolated, unrelated tables - 06-26-2007 , 02:58 PM

Re: Database design question - Isolated, unrelated tables - 06-26-2007 , 03:14 PM