3

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:

Model One

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:

Model Two

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).

  • 1
    Keep option 1 with no history and create an appropriate auditing structure. Sounds like you have two distinct requirements being filled by one multi-purpose design, which is causing you concerns. – Dave Mar 30 '16 at 21:21
  • What is your main concern? Performance? How many rows are you expecting in Requests? How often does the status change for a given request? What RDBMS is this for? – Max Vernon Mar 30 '16 at 21:29
  • @Dave I can't see why I would have option 1 with no history - I would then just remove the RequestStatus and then just have the StatusID on Request, and the audit effectively becoming RequestStatus as in the second diagram. – James Culshaw Mar 30 '16 at 21:48
  • 1
    @MaxVernon normally my main concern is getting the correct design, but performance does come into it. In the front end application the code is only ever concerned with the current status in 99.9% of cases. The only time that I need to show status history is when the user specifically asks to view it. This is why I am thinking the second design is the correct option, as the status history is really an audit trail. Maybe renaming RequestStatus to RequestStatusAuditTrail would be the correct thing as it clarifies the tables purpose. – James Culshaw Mar 30 '16 at 21:57
  • May be reading about slowly changing dimensions will help. – mustaccio Mar 31 '16 at 2:34
2

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;

enter image description here

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';

enter image description here

  • I used to use triggers a lot but for the past 10 years I have ditched them as they are a 'hidden' layer from the front-end application. I just handle it in the application wrapped in a transaction so everything can be seen in the application code, and can be easily debugged at the same time. – James Culshaw Mar 31 '16 at 1:46
  • 1
    You certainly don't have to use a trigger, but they do work well, and you never need to be concerned about some code-path not performing correctly. – Max Vernon Mar 31 '16 at 1:57
  • 1
    @JamesCulshaw - I've updated my answer to show how I'd approach the problem without using triggers. – Max Vernon Mar 31 '16 at 14:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.