Post reply

SQL DATABASE

  • January 22, 2015 at 10:53 pm

    #302501

    I have a matrix table. These status can be changed by the user and I want to capture each change in database with out updating the earlier status

    Pending

    Activated

    In PROGRESS

    Submitted

    Completed

    Pending can be changed to submitted or completed. For one form there can be different status at different time. And each status must be saved in the database table. How can I design a table

  • January 22, 2015 at 11:08 pm

    #1772489

    Without sample data, it's hard to see what you mean exactly. If you have a table of tasks or jobs, and then a child table of status updates, then this is easy...

    CREATE TABLE task (

    taskID INT IDENTITY,

    taskName VARCHAR(20) NOT NULL,

    CONSTRAINT uqTaskName UNIQUE(taskName),

    CONSTRAINT pkTask PRIMARY KEY (taskID));

    Then a child table of status updates:

    CREATE TABLE Status(

    taskID INT NOT NULL,

    StatusDate DATETIME NOT NULL,

    StatusState VARCHAR(20) NOT NULL,

    CONSTRAINT pkStatus PRIMARY KEY (TaskID, StatusDate),

    CONSTRAINT fktaskID REFERENCES task(taskID));

    If the status values are in a specified order, you could have a table of StatusName and StatusValue and have the StatusValue be some kind of number that you could increment (and sort by)...

  • January 22, 2015 at 11:20 pm

    #1772497

    Thank you

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply