473,836 Members | 1,539 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Database design question - Isolated, unrelated tables

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.

Jun 25 '07
12 7030


Tim <ti**********@h otmail.comwrote :
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>
I would not recommend the above for busy tables.

And there's a point to auditing tables that are relatively static -
normally lookups?
Paul...
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.
Jun 26 '07 #11
ny********@hotm ail.com wrote:
On Jun 26, 11:08 am, nyathan...@hotm ail.com wrote:
>>On Jun 26, 10:29 am, "Tom Cooper"

<tomcoo...@co mcast.no.spam.p lease.netwrote:
>>>In addition to the other replies, I would add that foreign key constraints
are just one of many tools thedatabasedesi gner can use to help ensure that
bad data does not get placed in yourdatabase. Other tools include check
constraint s, 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
significan t 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.comwrot e in message
>>>news:11***** *************** **@g37g2000prf. googlegroups.co m...
>>>>Hi,
>>>>I have a question regarding best practices indatabasedesig n. In a
relationald atabase, is it wise/necessary to sometimes create tables
that are not related to other tables through a foreign Key
relationshi p 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
operation s 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(sep arate 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 thedatabaseitse lf
(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
unauthorize d 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
maintaini ng/designing two different schemas.
>>>>I had a look at the microsoft adventureworksd atabaseschema 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
requirement s 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
transaction s and changes that happen in the table right?

Crystal reports were being considered at some stage for more
sophisticat ed reports, but for now they want some sort of entries in
there to see whats happening (not necessarily at thedatabaseleve l,
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 thedatabaserigh t 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.
Jun 26 '07 #12
On Mon, 25 Jun 2007 21:07:00 -0700, ny********@hotm ail.com wrote:
>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,
SecondForeignKe y int NULL,
-- Other columns,
PRIMARY KEY (SomePrimKey),
FOREIGN KEY (FirstForeignKe y) REFERENCES SomeTable,
FOREIGN KEY (SecondForeignK ey) REFERENCES OtherTable,
CHECK ((FirstForeignK ey IS NULL AND SecondForeignKe y IS NOT NULL)
OR (FirstForeignKe y IS NOT NULL AND SecondForeignKe y IS NULL))
);

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Jun 26 '07 #13

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

16
7529
by: noah | last post by:
Does PHP have a feature to associate Cookie sessions with a persistent database connection that will allow a single transaction across multiple HTTP requests? Here is how I imagine my process: I have an series of interactive HTML forms. The user begins a Cookie session. A database connection is opened and a transaction is begun. After the user goes through any number of pages where they update the database they finish on a page where...
6
1943
by: cover | last post by:
If you're writing many databases that aren't necessarily associated with each other (ie parts, vacation days, how you like your steak done, and school you attended, etc; as examples), does it make more sense to have one database name and several tables for the data topics above OR multiple databases since they aren't associated with each other? It would SEEM easier to have a single database with multiple tables from a data management...
3
4521
by: Rushikesh | last post by:
I am designing a WEB BASED Accounting Software with ASP and SQL Server. For this I need some help for the Database design. My design is as follows. I)User table: User_id, UserName..... Users (e.g. John Smith) Each User would contain a following Group of tables a)Customers
1
4065
by: mksql | last post by:
As an example, I am building an authentication mechanisim that will use data in the 3 left tables to determine rights to objects in a destination table, diagrammed below. In this structure, multiple records in the left tables will point to multiple records in the right table. Normally, I would approach this problem using junction tables (LeftID, RightID) to create many-to-many joins. However, given the structure of each table is nearly...
6
4261
by: Mike Wiseley | last post by:
We recently converted our department wide shared Access97 database to Access2K. We used to be able to open various reports in design mode and make changes to the design (or create new reports) even while other users in this shared database might be using other, unrelated reports. Now, in Access2k, it appears that whenever you do not have exclusive access, you cannot make any design changes to reports. It seems that the presence of any...
3
1673
by: James Armstrong | last post by:
Hi all, (warning - long post ahead) I have been tasked with designing a database for my company which will store trade information (it is a financial firm). It will need to export this info into an excel file while converting some of the data into an export format (example - we use B for buy, the firm we export to uses BY). Eventually, accounting will also need reports from the data.
7
1899
by: perspolis | last post by:
hi I have two table named Purchase and Sale..all of fields of both tables are the same...I make them design in one table with an additional boolean field to determine which is Sale and Purchase... it is good to design them in seperate tables without additional field?? or design both in one table...
22
3535
by: amygdala | last post by:
Hi, I'm trying to grasp OOP to build an interface using class objects that lets me access database tables easily. You have probably seen this before, or maybe even built it yourself at some point in time. I have studied some examples I found on the internet, and am now trying to build my own from scratch. I have made a default table class (DB_Table), a default validation class (Validator) which is an object inside DB_Table and my...
10
7648
by: teddysnips | last post by:
My clients have asked me to maintain a database that was developed in- house. It's pretty good, considering the developer isn't a "programmer". The first thing they want me to do is to split it into a Front End/ Back End, which is very sensible. However, there are a number of ad hoc queries that are required to be available to all users, which can be edited, deleted or created "on the fly". The most sensible solution would seem to be...
0
9671
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
1
10596
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10255
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9382
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
6980
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5829
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4459
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4021
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3116
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.