How to represent current state or status and maintain data/referential integrity? - Database Design

I have a table that maintains specific business transactions, let's call it LOANS. Each record can at any given time have one of several "states", for example "in re-payment / open", "defaulted", "voided", or "paid".

The status of a record can only change due to an "event" or action taken by the user of the application (some events do not cause a state change, eg a payment modifies the record but does not change the state). While keeping track of the current status, it is also important that we can easily track the "event" that put the transaction in this state.

I have seen other databases setup such that LOANS contains a status column, and a letter (or even a foreign key to a table of possible states) is used to designate current status. Then the EVENTS table has a column that is a FK to LOANS.

This solution works, but I am concerned that this allows for a possible loss of data integrity. In other words, there is nothing to prevent status from being set to "defaulted" with no EVENT to support the change. It would be left solely to the application to ensure this never happens (the开发者_如何学Cre is no referential integrity in this solution as I see it).

My alternate solution is to scrap the status column in LOANS and instead use a lastEvent column which is an FK to EVENTS. The EVENTS table would then maintain the "type" of event which would in turn describe the state change (eg if the lastEvent was of type 'payoff' then naturally the state is 'paid').

My questions are:

1. Should I even be concerned about the data integrity (is this such a common thing to do because it is ok to do it)?
2. Is my proposed solution sound, or is there a better solution?
3. Are there any pitfalls in my solution that I should be aware of?

In case it matters, I am using MS SQL SERVER 2005.

---

1. Only you know what the risks are if you don't have referential integrity here - but in general, yes, it's worth designing your database so you can use referential integrity. It reduces errors, and increases the chances future developers will understand your schema.

2. I'd have a schema along the lines of...

   Loans

   ---

   Loan_Id (PK)

   amount ....

   Events

   ---

   Event_id (PK)

   Loan_ID (FK)

   Event_type_id (FK)

   Date

   status_id (fk)

   event_type

   ---

   event_type_id (pk)

   description

   status

   ---

   status_id (pk)

   description

To find the current status of any load, you'd need to find the newest entry in the events table for that loan. To see the history of a loan's status changes, you can query the events table.

When you record events which do not change the status, the record with the new event simply contains the current status of the event - so you might get a table like:

Event_id   Loan_ID Event_type_id    Date         status_id 
 ------------------------------------------------
 1               1           NEW   1 Jan 2011   NEW
 2               1       APPROVE   2 Jan 2011   NEW
 3               1       DEFAULT   1 Feb 2011   DEF
...

This way, you always retrieve the current status by finding the latest record.

It dramatically reduces the risk of bugs - if your logic for "events" changes, and an event does change the status of a loan, you only have to change this in one place - the code which creates the record in the event database. You don't have to remember to also change the "loan" record.

As for performance - I'd only optimize for this if you really need to - on a well-tuned database, the join and max should not be show stoppers.

You can refine the model by creating a table which maps valid status transitions and their associated events; depending on your database, you could enforce this through triggers.

I think this is better than a "last event" foreign key, because it's redundant - by definition, the last event is the record with the MAX(date) for that loan.

---

I think your concern about integrity is valid. You have specified a business rule that states, actually Status is not a writeable property it is inferred - from events.

I'd make it a computable column based on your Events data.

Can only guess your db implementation, so the other alternative is a trigger updated column but how would you then make sure its read-only? (presumably with locks/permissions)