[Solved] Best and Efficient way to manage steps in a relational database? [closed]

Question

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

Accepted Answer