Search code examples
sqldatabase-designhierarchical-data

Best and Efficient way to manage steps in a relational database?

The database I'm designing, needs to store steps that a person go through doing certain activity and when he will stop:

enter image description here

The schema is above so you can understand better.

I was thinking about something like this:

Step table

 Step
 ---
 StepId
 StepTypeId

stepType table

stepType
---
idstepType

stepTransition table to track the steps

stepTransition
---
stepSource
stepTarget
stepTransitionType

or a stepTracking table like this:

stepTracking
---
path
numberofTimesTaken int
stoppedbyUser bool
stoppedbySystem bool

any idea how to improve it if the data are numerous or any thoughts ?

And the idea of this is to track and know how many people went through each step and when they did stop, etc.

to finally be able to do some statistics on which steps the users stop the most.

Solution

  • You have a User table with a User ID as the primary key.

    You have a Step table with a Step ID as the primary key. Something like this.

    Step
----
Step ID
Step Name

    Then you have a junction table that captures the many to many relationship between users and steps.

    UserStep
--------
UserStep ID (PK)
User ID
Step ID
Start timestamp
Completion timestamp

    Where you have a unique index on (User ID, Step ID) to associate users with steps. You can also have a unique index on (Step ID, User ID) to associate steps with users.

    An alternative UserStep table would look like this.

    UserStep
--------
UserStep ID (PK)
User ID
Step ID
Status code (start, complete)
Timestamp

    This way, you can have multiple statuses in a Status table. (Start, waiting for approval, waiting for resources, waiting for a co-worker, waiting for a managerial decision, waiting for government action, complete).