Hey, I'll try to keep this brief ()... *EDIT: Fail...*

A (well, the...) database I'm currently designing needs to capture information about changes made to it. The problem is, I can see too many different ways to do it...

Tables in question: Item, Relationship (between instances of Item) and Attributes (EAV!).

Please suggest a standard method of logging such changes as:

<User> updated <Item> changing <Attribute> from <Value> to <Value>
or...
<User> added <Item>
or...
<User deleted <Item>

There is an element of audit trailing (who did what) and an element of historical data (how things were).

My ideas (so far):

Eventlog(EventLogID, Item_ID, EventType_ID, EventTimestamp, Username, EventlogValue) - only good for seeing who did what to which item, when... Elements of change are obscured / lost / bundled into EventlogValue attribute, which may be a problem. Also, I might require a second table to log Relationship events (the above definition only caters for Item).

Extend the horrible EAV attribute table (it's staying, that's a given!) to have historic attribute types, e.g. <Entity> <Attribute: Name> <Value> and when it's updated, change it to <Entity><Attribute: PreviousName> <Value>. I could add a date column (EAV-D, anyone? ) to put the changes in context / historical order but... Eugh, this is making a bad thing worse?

Extend the Item, Relationship and Attribute tables to have both a Status (bitwise or foreign key to some status lookup table) attribute and a StatusChanged (timestamp) so I would never delete anything (yay for historical data) but instead set its status to off / deleted and update the timestamp. This could spiral way out of control though... I guess I could use / abuse a trigger to make sure the count of historical data for a given item (of a given type) doesn't exceed some value though, and delete older data where it does. I'd also need to think about adding in WHO did what, too.

Move values to historical versions of tables (which are the same, but timestamped etc., probably) and keep the up-to-date data completely separate from the historical data.

There are more, but this is long enough already...

Thanks in advance guys.

the solution is, why are you doing it, what information do you hope to get out of it, and how much disk space do you gots

A fine point...

Why / What: to track who is responsible for changes (or errors, more likely); to keep historical data to see details of previous data (e.g. the previous names an item had) and how an item previously interacted with other items before it was deleted.

Disk Space: All in-house stuff, no defined limits (yeeeah)... The company has resources to keep a LOT of historical data. There are existing maintenence / backup routines on all the DB servers, so some housekeeping could be completed in the future, if things scaled out of control, without too many problems. It's worth noting, probably, that this is a reasonably small-scale application with around 10 users with write access, with changes only required (beyond initial population) to reflect updates in infrastructure, so updates should be fairly few.

Furthermore, it's looking like having historical versions of said tables (with appropriate additional attributes such as timestamps) is the most sensible approach, as to leave the current data separate. However, I need to spend some time thinking how foreign key references would / should behave... I could escape those problems by leaving the historic data in with the live data and using a status column (so all foreign keys would resolve), but then I have to consider other issues...

Hmm.

which of your many design scenarios actually accomplishes this? probably all of them, right?

the way to find out is to write the queries for a typical previous data retrieval requet for each scenario, and compare the complexity of the SQL

Yeah, which is why I posted in the first place... Doubts flourish in the presence of choice.

You're right though - if none of the above seem like stupid ideas I'll have a play. I was kind of expecting "OMG!1eleven dont do that its bad practice" for at least a couple of them.

Cheeeers...

EDIT: I've just realised the foreign keys shouldn't really present any problems... The keys should just exist between the parallel (backup) tables in the same way they do the live (but as different foreign keys, technically). Triggers would ensure that primary keys were consistent accross live / backup tables, so the referenced entry would always exist. That changes things... </thinking-aloud>

The historical data aspect is a very common problem, and which of the possible approaches is suitable depends on your requirements. Google for "slowly changing dimensions".

Thanks for the tip... A bit of research later and I'm exactly where (I think) I need to be with this. I'm surprised I've not come accross SCD before. At least a lot of the concepts came naturally.

I'm going to proceed with Type 2 SCD, aka adding such attributes as EffectiveDate, ObsoleteDate and Username to the relevant relations, creating new tuples to reflect changes. The combination of historic data, audit trailing and apparent lack of complexity makes it a suitable choice, in my opinion.

Cheers!

The next concept I'm dealing with (in a similar vein...) is that of lookup table values persisting throughout the lifetime of the database... It's obvious that through maintaining historical data that there'll be some foreign key references within this data which point to values in the lookup tables which would / could have otherwise been deleted, if it wasn't for maintaining referential integrity. I see this to be an issue because I may populate drop down list controls etc. from these lookup tables to make data entry a bit easier, and I don't want users to be able to input irrelevant data.

This is where programming practices are skewing my judgement a bit... It seems obvious to add some IsCurrent bitwise attribute to the lookup tables and filter values accordingly, but I don't know which of the following *is considered better practice*... I'm aware that both solutions will work. Maybe it doesn't even matter. Anyway:

LookupTable(ID, Description, IsEnabledBit)

Or...

LookupTable(LID, Description)
LookupTableVisibility(LVID, L_ID, IsEnabledBit)

Where the latter aims to join the two tables on LID in order physically separate the actual data structure from any UI elements. However, it'd be a 1:1 relationship, which suggests it could / should be in the same table.

Best-practice-related advice, please.

Thanks...

EDIT: The same thing also applies for sorting / ordering lookup table values... I'm guessing I'll have to introduce some attribute to control the order explicitly where no natural, desirable sorting order exists.