Re: logging changes for nested tables

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Rick Sawtell (ricksawtell_at_hotmail.com)
Date: 09/02/04 

Date: Thu, 2 Sep 2004 07:42:53 -0700

Without going into great detail, how about creating a separate table to
store the old data.

Then you could create triggers on your 3 tables. When they are updated, the
trigger can fire and move the original data to the separate table.

Rick Sawtell
MCT, MCSD, MCDBA

"lukasz" <bbla32@op.pl> wrote in message
news:%237w1TDOkEHA.1904@TK2MSFTNGP09.phx.gbl...
> I need to create 3 tables:
> Sites
> Employees
> PhoneNumbers
>
> each containing certain properties (like description & name), and I want
to
> not only track all changes made to the tables but also be able to retrieve
> previous versions. I thought of designing the columns as follows:
>
> Sites: RowID (PK), SiteID, Name, Description etc., WhoChanged,WhenChanged
> etc.
> Employees: RowID (PK), EmployeeID, Name, ..., WhoChanged, ...
> PhoneNumbers: RowID (PK), PhoneNumberID, Name, ..., WhoChanged, ...
>
> because SiteID, EmployeeID, PNID are not unique, the tables are not
related.
> Editing for example an employee adds a new row to the Employees table with
> the same EmployeeID as previously, but new data (Name, ...) and log info
> (WhoChanged, ...), and RowID is autoincreased. Deleting an employee causes
> adding a new row with same EmployeeID and null data (Name, ...). Etc.
>
> Now, listing all phone numbers would be about first taking all sites then
> all employes for these sites and finally all phone numbers for these
> employes. Where "all" means grouping sites by SiteID (employees by
> EmployeeID, PN by PNID), and taking out these rows with highest RowID for
> each SiteID (EmployeeID, PNID), and finally filtering out rows with all
null
> data fields. And I need to retrieve whole structure consisting of the 3
> tables at a time. Also I need an option to retrieve previous versions of
the
> structure, e.g. the version that was actual on 2003-12-02.
>
> Are there better designs? I don't like the idea there're no FK's in the
> tables. However, I wouldn't like the idea of adding extra tables for
storing
> changes only, I'm even not sure whether that'd work (considering nested
> changes).
>
> Lukasz
>
>

Relevant Pages

  • Re: Training records
    ... EmployeeID ... Do supervisors ever take courses? ... "an option group in tblCourse is one way of accomplishing what I assume is ... Could use separate look up ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Training records
    ... EmployeeID ... Do supervisors ever take courses? ... "an option group in tblCourse is one way of accomplishing what I assume is ... Could use separate look up ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Training records
    ... EmployeeID ... Do supervisors ever take courses? ... "an option group in tblCourse is one way of accomplishing what I assume is ... Could use separate look up ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Training records
    ... Do supervisors ever take courses? ... Could use separate look up tables ... EmployeeID ... group in tblCourse is one way of accomplishing what I assume is the need to ...
    (microsoft.public.access.tablesdbdesign)
  • logging changes for nested tables
    ... because SiteID, EmployeeID, PNID are not unique, the tables are not related. ... , and RowID is autoincreased. ...
    (microsoft.public.sqlserver.server)