Here are my two tables...

Object Table:

CREATE TABLE [dbo].[tbl_Folder](
[FolderID] [int] IDENTITY(1,1) NOT NULL,
[FolderName] [char](12) NOT NULL CONSTRAINT
[DF_tbl_Folder_FolderName] DEFAULT (0),
[LastEventID] [int] NULL,
[PurgedFlag] [char](1) NOT NULL CONSTRAINT
[DF_tbl_Folder_PurgedFlag] DEFAULT ('N'),
[InsertBy] [varchar](50) NULL,
[InsertDateTime] [datetime] NULL,
[UpdateBy] [varchar](50) NULL,
[UpdateDateTime] [datetime] NULL,
[DeleteBy] [varchar](50) NULL,
[DeleteDateTime] [datetime] NULL,
[rowguid] [uniqueidentifier] ROWGUIDCOL NOT NULL DEFAULT (newid()),
CONSTRAINT [PK_tbl_Folder] PRIMARY KEY CLUSTERED
(
[FolderID] ASC
) ON [PRIMARY]
) ON [PRIMARY]

Event Table:

CREATE TABLE [dbo].[tbl_EventLog](
[EventID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[FolderID] [int] NOT NULL,
[EventType] [varchar](50) NOT NULL,
[FromDepartmentType_EmployeeID] [int] NOT NULL,
[ToDepartmentType_EmployeeID] [int] NOT NULL,
[EventDateTime] [datetime] NOT NULL,
[InsertBy] [varchar](50) NULL,
[InsertDateTime] [datetime] NULL,
[rowguid] [uniqueidentifier] ROWGUIDCOL NOT NULL DEFAULT (newid()),
CONSTRAINT [PK_tbl_Event] PRIMARY KEY CLUSTERED
(
[EventID] ASC
) ON [PRIMARY]
) ON [PRIMARY]

EventDateTime is the time of the event. Then InsertDateTime is the
audit time. The rowguid's are used for replication.
The LastEventID on the folder table is a FK. As is FolderID on the
event table.

Thanks,

Oran

Hi Oran,

If you have a LastEventID on tbl_Folder, why do you need the same
functionality on tbl_EventLog? Aah - going back to your original
question, it looks like you've implemented the "add a LastEventID
column to tbl_Object" approach

Maybe I'm just getting pedantic about the naming, but for me a log
table is there to record the where's and when's of events occurring.
Adding history tracking to a log table seems like a pretty unusual
thing to have to do, as the history is already intrinsic to the data.

You still haven't really gone into detail on how these tables are/will
be used... As always, there are a number of ways to produce the
results you are after, the "best" way comes down to balancing your
costs vs requirements equation.

Good luck!
J

Hi J,

The requirements for the project were to be able determine the status
and location of the Folder (the object) at all times and to track a
history of what has happened to this folder in the past.
The users were really only interested in knowing the current status
and location of a folder. The history requirement was something that
they mentioned would be nice but they didn't really care what it
looked. At the time I had never worked with history or log tables and
I had some deadlines to meet so after consulting a co-worker I put
this tbl_EventLog together (he reccommended the "log" being added on).

There are about 15 event types that can happen to a folder and there
are rules of which events can happen when. After an event occurs the
Location and Status change of the folder. Location is based on where
the event ended up, the "ToDepartmentType_EmployeeID" column. The
status depends on the "EventType" of the event that just occurred.

Looking back at what I did I know I missed a number of things.

1) The history/log should be done differently. Some people
reccommended two tables. One to store the active data and one that
logs the history.
2) There are two levels of statuses. Some statuses stay with a folder
and only get changed by certain events while the level statuses will
be changed by different events.

For example, the event "check out" happens to a folder. The
status is "checked out". The event "check in" will change the status
to "checked in." While it is "checked in" the folder can have the
event "purge" happen to it which
gives the folder the status of "checked in" and "purged."
If the folder now has the "check out" event occur it will be "purged"
and "checked out."

I didn't realize this untill the middle of development and it was
already too late to change the design. Looking back I am trying to
think of the best way to design this.

I hope I made it clearer what the tables are used for.

What is the difference b/n a "log" and "history"?

Would you have three separate tables for current data, log, and
history?

Thanks,

Oran

Hi Oran,

In my view (note - this is just my opinion):
Log - an ordered record of what happened when
History - a record of the state of your data at a given point in time.

The difference is that a log would be used to satisfy the questions
"What happened next?", or "What happened at this time?", a history
would satisfy the question "At this point in time, what did my data
look like?" The difference is pretty subtle, and in theory they are
interchangeable (you can derive the log from the history, and vice
versa) - so really it comes down to your requirements, and the cost
equation. Storing history data is never a bad idea in a database, it
is up to you to decide whether it will cost too much to implement in
your project. With unlimited resources I would choose to store all
history all the time - in reality this is rarely a possibility

WRT the statuses .. a quick-fix could be to simply add statuses for
all status combinations. So you could have a single status which
means "checked in and purged" or "checked out and purged". I'm fairly
sure Celko can give you a long list of reasons why this is not a good
idea though If the relationship between Folder and Status is many-
to-many, make it so in your database.

<quote>
Would you have three separate tables for current data, log, and
history?
</quote>

It sounds like you're developing some kind of document management
system (or maybe you've changed the names of your objects to
illustrate your point?) - if this is the case I would imagine that
history tracking/audit capability is fairly important in this
application. I see you already have a number of audit columns on your
Folder table (Inserted/Updated/Deleted ...). Something to remember
here, is that even though you're tracking insertion and deletion - for
updates you are only going to have the audit details of the *last*
update to run against the table. It might be better to implement the
table more like:

CREATE TABLE [dbo].[tbl_Folder](
[FolderID] [int] IDENTITY(1,1) NOT NULL,
[FolderName] [char](12) NOT NULL CONSTRAINT
[DF_tbl_Folder_FolderName] DEFAULT (0),
[LastEventID] [int] NULL,
[PurgedFlag] [char](1) NOT NULL CONSTRAINT
[DF_tbl_Folder_PurgedFlag] DEFAULT ('N'),
[CreatedBy] [varchar](50) NOT NULL,
[CreatedOn] [datetime] NOT NULL,
[ValidFrom] [datetime] NOT NULL, -- Valid from is NOT NULL
[ValidTo] [datetime] NULL, -- This is NULLable with the NULL
row being the "current" one.
[rowguid] [uniqueidentifier] ROWGUIDCOL NOT NULL DEFAULT
(newid()),
CONSTRAINT [PK_tbl_Folder] PRIMARY KEY CLUSTERED
(
[FolderID] ASC
) ON [PRIMARY]
) ON [PRIMARY]

You don't need Inserted and Updated data as you are only inserting and
this is reflected by the CreatedBy/On columns. Deleted is also
unnecessary - when a row is deleted, simply set the ValidTo datetime
and don't insert a new row. When you query the data, the row will not
meet the "WHERE ValidTo IS NULL" constraint. Regarding current data -
I would do this using a VIEW, not a whole new table.

I hope this helps!
J

Hi J,

Thank you for all of your suggestions and explainantions. It is very
helpful.
I guess this is a document management system but it is for physical
folders not files on a computer.

This was actually my work around. I made statuses like "Purged Check
Out" and "Purged Check In."

How would it look if I was actually updating a record?
Would the ValidTo get the current date and a new record is created
with ValidTo being NULL?
Or add more fields?

So it would sound like the tbl_EventLog would be classified as a "Log"
table. I don't have any history tables with data of what happened at a
certain point in time.

Thanks,

Oran

PAPER!? They still make that stuff?

You might want to look at actual document management systems rather
than RDBMS. Another system to look at is "Shephardization" or talk
to a law student. This is the system used for USA court cases which
links decisions together to give a complete picture of the state of
affairs.

http://www.19thcircuitcourt.state.il..._searching.htm

Hi Oran,

<quote>
How would it look if I was actually updating a record?
Would the ValidTo get the current date and a new record is created
with ValidTo being NULL?
</quote>

That is exactly right Your Create* columns show you who made the
change (insert/update/delete's are all just changes to the current
state of your data), and the Valid* columns reflect the time period
for which the row was "current". So for an update, you simply
timestamp the ValidTo column in the old "current" row and insert a new
row with a NULL ValidTo column.

<quote>
So it would sound like the tbl_EventLog would be classified as a
"Log"
table. I don't have any history tables with data of what happened at
a
certain point in time.
</quote>

It does sound like EventLog is a true "log", but I would probably keep
full history on the Folder table.

<quote>
PAPER!? They still make that stuff?
</quote>

Hehe - Celko has a sense of humor?!?

Good luck!
J

You have both mentioned that is a good idea to use a view to hold the
current data.
I assume that in this case the view would Join the Folder with the
LastEvent that occured to the Folder.

I am using SQL Server 2000 sp3.

I am new to views. I have been reading up on them and trying to
determine whether the data in the view gets refreshed when the
underlying tables get new records or do you have to execute the view
everytime you refresh it?

If the latter is true then what advantage does the view have over
exceuting a stored procedure?
The view would have to be refreshed every time I need to know the
status of a folder b/c new events are inserted all the time.

Thanks,
Oran

Ok, I just tried it myself (should have done this first).

The view did have the most current data in it.

Oran

I don't know if the previous post worked, but I figured out from
testing the view that the data is refreshed.

Oran