Re: Database design question - Isolated, unrelated tables

<nyathancha_at_hotmail.com> wrote in message news:1182755325.216207.318140_at_g37g2000prf.googlegroups.com...
> 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. Received on Mon Jun 25 2007 - 12:48:03 CEST