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