Database design decision help

Question

I currently have a database design that I have been looking at and I am wondering if its the best one.

I have the following tables:

Each request has a current status. I need to keep track of all the status changes. RequestStatus currently keeps all these records, with the current record being marked as being the current one (to remove the need to find the one with the most recent date).

Looking at it I am beginning to think that I should have a StatusID column on Request changing the relationships to:

I know this breaks proper normalisation but what I really need is just an audit trail or status changes which is only ever accessed via Request - I will never need to access RequestStatus via Status.

Thoughts?

UPDATE:

There will be 6 statuses total: Pending, Approved, Rejected, Cancellation Requested, Cancellation Approved and Cancellation Rejected.

Most Requests will have 1 or 2 statuses total with the majority being 2 e.g. Pending then either Approved or Rejected or straight to approved (for automatically approved requests).

Answer 1

If I were to do this, I'd use SQL Server, with 3 tables.

USE tempdb; /* this is a test-bed */

IF COALESCE(OBJECT_ID('dbo.RequestStatusHistory'), 0) <> 0
BEGIN
    DROP TABLE dbo.RequestStatusHistory;
END
IF COALESCE(OBJECT_ID('dbo.Requests'), 0) <> 0
BEGIN
    DROP TABLE dbo.Requests;
END
IF COALESCE(OBJECT_ID('dbo.Statii'), 0) <> 0
BEGIN
    DROP TABLE dbo.Statii;
END

CREATE TABLE dbo.Statii
(
    StatusID INT NOT NULL
        CONSTRAINT PK_Statii
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , StatusDescription VARCHAR(50)
);

INSERT INTO dbo.Statii(StatusDescription)
VALUES ('Pending')
    , ('Approved')
    , ('Rejected')
    , ('Cancellation Requested')
    , ('Cancellation Approved')
    , ('Cancellation Rejected');

CREATE TABLE dbo.Requests
(
    RequestID INT NOT NULL
        CONSTRAINT PK_Requests
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , RequestName VARCHAR(30) NOT NULL
    , CurrentStatusID INT NOT NULL
        CONSTRAINT FK_Requests
        FOREIGN KEY 
        REFERENCES dbo.Statii(StatusID)
    , CurrentStatusDate DATETIME NOT NULL
        CONSTRAINT DF_Requests_CurrentStatusDate
        DEFAULT (GETDATE())
);

CREATE TABLE dbo.RequestStatusHistory
(
    RequestStatusHistoryID INT NOT NULL
        CONSTRAINT PK_RequestStatusHistory
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , RequestID INT NOT NULL
    , StatusID INT NOT NULL
    , StatusDate DATETIME NOT NULL
        CONSTRAINT DF_RequestStatusHistory_StatusDate
        DEFAULT (GETDATE())
);
GO

I'd create a couple of stored procedures, however you could easily do these in the application code if desired. Both procedures use a single atomic statement that negates the need for an explicit transaction since either the entire transaction will commit, or it will entirely roll back.

I tend to recommend using stored procedures for DML operations wherever possible since this allows you to make database level DDL changes without the requirement of deploying new binaries everywhere.

The first proc simply inserts a new row into the dbo.Status table, and returns a resultset including the new RequestID, the CurrentStatusID (which is set to 1), and the CurrentStatusDate which is set to the current date and time using GETDATE():

IF COALESCE(OBJECT_ID('dbo.StatusInsert'), 0) <> 0
BEGIN
    DROP PROCEDURE dbo.StatusInsert;
END
GO
CREATE PROCEDURE dbo.StatusInsert
(
    @RequestName VARCHAR(30)
)
AS
BEGIN
    INSERT INTO dbo.Requests (RequestName, CurrentStatusID, CurrentStatusDate)
    OUTPUT inserted.RequestID, inserted.CurrentStatusID, inserted.CurrentStatusDate
    VALUES (@RequestName, 1, GETDATE()) ;
END;
GO

The second proc allows us to do 2 operations in one atomic unit. First, it updates the CurrentStatusID and CurrentStatusDate columns in dbo.Requests. Then, using the OUTPUT clause, it inserts the old values from dbo.Requests into dbo.RequestStatusHistory.

IF COALESCE(OBJECT_ID('dbo.StatusUpdate'), 0) <> 0
BEGIN
    DROP PROCEDURE dbo.StatusUpdate;
END
GO
CREATE PROCEDURE dbo.StatusUpdate
(
    @RequestID INT
    , @NewStatusID INT
)
AS
BEGIN
    UPDATE dbo.Requests 
    SET Requests.CurrentStatusID = @NewStatusID
        , Requests.CurrentStatusDate = GETDATE()
    OUTPUT deleted.RequestID, deleted.CurrentStatusID, deleted.CurrentStatusDate
    INTO dbo.RequestStatusHistory(RequestID, StatusID, StatusDate)
    WHERE Requests.RequestID = @RequestID
END;
GO

Insert some sample data:

EXEC dbo.StatusInsert 'Request #1';
WAITFOR DELAY '00:00:00.500';
EXEC dbo.StatusInsert 'Request #2';
WAITFOR DELAY '00:00:00.500';
EXEC dbo.StatusUpdate 1, 2;
WAITFOR DELAY '00:00:00.500';
EXEC dbo.StatusUpdate 1, 3;
WAITFOR DELAY '00:00:00.500';
EXEC dbo.StatusUpdate 2, 4;

Show the results:

SELECT *
FROM dbo.Requests;

SELECT *
FROM dbo.RequestStatusHistory;

When the client wants to see status history, you could provide those details using a reasonably flexible stored procedure such as:

IF COALESCE(OBJECT_ID('dbo.RequestHistory'), 0) <> 0
BEGIN
    DROP PROCEDURE dbo.RequestHistory;
END
GO
CREATE PROCEDURE dbo.RequestHistory
(
    @RequestID INT = NULL
    , @RequestName VARCHAR(30) = NULL
)
AS
BEGIN
    ;WITH ReqHistory
    AS
    (
        SELECT r.RequestID
            , r.RequestName
            , StatusDescription = s.StatusDescription
            , StatusDate = r.CurrentStatusDate
        FROM dbo.Requests r
            INNER JOIN dbo.Statii s ON r.CurrentStatusID = s.StatusID
        UNION ALL
        SELECT r.RequestID
            , r.RequestName
            , s.StatusDescription
            , rsh.StatusDate
        FROM dbo.Requests r
            INNER JOIN dbo.RequestStatusHistory rsh ON r.RequestID = rsh.RequestID
            INNER JOIN dbo.Statii s ON rsh.StatusID = s.StatusID
    )
    SELECT *
    FROM ReqHistory rh
    WHERE (rh.RequestID = @RequestID OR @RequestID IS NULL)
        AND (rh.RequestName = @RequestName OR @RequestName IS NULL)
    ORDER BY rh.RequestName
        , rh.StatusDate;
END
GO

Running the RequestHistory proc against the sample data:

EXEC dbo.RequestHistory @RequestName = 'Request #2';