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!
JHi 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|||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|||>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 [sic: rows are not records!] or do you have to execute the view every time you refresh it? <<
A VIEW is a virtual table. Whenever you invoke its name, the
optimizer decides if it wants to make it into a materialized table in
physical storage, or if it wants to expand it in the query like an in-
line macro. Either way, its data is always current when you use it.
It is refreshed only if it has to be.
