-4

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.

Arb
  • 77
  • 10
  • Is closure table the best shot ? – Arb Mar 08 '21 at 09:28
  • 1
    [How can you represent inheritance in a database?](https://stackoverflow.com/q/3579079/3404097) [What are the options for storing hierarchical data in a relational database?](https://stackoverflow.com/q/4048151/3404097) [How to create a MySQL hierarchical recursive query](https://stackoverflow.com/q/20215744/3404097) [How to Optimize Queries in a Database - The Basics](https://stackoverflow.com/q/3191623/3404097) [What are your most common sql optimizations?](https://stackoverflow.com/q/1332778/3404097) – philipxy Mar 08 '21 at 14:31
  • Fare well @philipxy I'll try to use words, phrases & sentences to explain my issues even thought that's what I usually do, it's just I lack vocabulary I suppose. English is my third language by the way. thanks for taking your time to try to explain it to me. but I have to create another account to try to use your tips again.. – Arb Mar 08 '21 at 14:51
  • 1
    You can edit this post. You can ask at [meta]. Again, you have not written enough here to be clear, it is not a matter of vocabulary. You don't show research. It is against the site rules to subvert bans. But I'm repeating myself. Good luck. – philipxy Mar 08 '21 at 15:16

1 Answers1

1

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

Gilbert Le Blanc
  • 50,182
  • 6
  • 67
  • 111
  • We can't track when the users stop at a certain step using your proposition can we ? – Arb Mar 08 '21 at 13:13
  • The purpose that I seek is to do some statistics on which step the user stops the most to be clear. – Arb Mar 08 '21 at 13:16
  • @Arb: It depends on your use case. That's why I included two UserStep tables. The end of a step can be equivalent to the start of the next step. Or your steps can have discrete non-contiguous intervals. Depending on your needs, a two should have one timestamp or two timestamps. You edited your question after I posted my answer. – Gilbert Le Blanc Mar 08 '21 at 15:09