how to save latest state of an entity in database with a proper db constraint

Question

I browsed a few questions and got to write my own, 3 to be exact. My db is simple I am rather a sort of a guy who wants things to be constrainted by each module so if any other module mal functions it doesnt spoil the logic.

What I have

I have a table Book, and I want to keep track of its status, there is a BookStatus table which can have statuses of book like Accepted, InProduction, Typeset, Printed etc. this table has the following schema

BookStatus [BookID, StatusID, Date]

The date field is nullable which indicates the date this status was ended, if it is null it means this is the current status. So to get the current status we do the following query:

*Select * from BookStatus where BookID = 123 and Date is null*

Problem

The problem is that there is no constraint in the table where we can have not more than one null field against a BookID, for a book can only be in one status at a time. So when there are tens of applications updating db plus manual intervention as well, we run into scenarios where a Book has multiple nulls against a status or not any null sometime.

Solution I have

The only thing I can think of is to add a Currentstatus field in Book table and whenever I update the status I update the Book table as well, but this doesnt solve my problem either.

Question

I want to know if

1. The current approach is good enough approach

2. Are there any other approaches which could ensure data integrity in current scenario

Answer 1

With current design:
If your application is under heavy concurrency you need to use concurrency control.
Without that Lost updates will be inevitable.
Known methods to control the cuncurency will be:

• Optimistic: Checking of whether a transaction meets integrity rules, like adding a time-stamp like column (row-version) to any table, checking if the updating records time-stamp is equal to existing rows time-stamp.
  Users will suffer with aborted transaction due to time-stamp mismatch.
  Not applicable in heavy transnational environment.
• Pessimistic: Serializing access to the shared resource, like locking the table record for every transaction.
  Performance reduction and encountering time out exception will be the side effect.

New design:
Prevent updates on heavy concurrent resources (tables).
Alter the design of BookStatus (I prefer BookTransaction), that any transaction will result an insert DML.
transaction date field will be mandatory.
Last status of the book will be extracted by a query or view, based on last transaction date or a sequence.

I encountered the problem in Banking industry when Accounts balance where shared between debit/ credit operations, starting from different platforms like bank-branch .Net clients, internet-bank J2EE web-services, card-switch C based hosts ... two face commits.
We tried all solutions. The new design saved us.

Note that none of solutions are perfect with no side effects.