0

I want to create an app where a user will go through a series of steps every day. All the subsequent step will depend on the previous ones, that is, 2 users can have different experiences depending on the choices they make every day in my app.

Something like 

   if .... then .... => 
      ..... if ... then =>
           ... if ... then =>
      ..... elseif ... then =>
      ..... elseif ... then =>

      ..... default ... then =>

   else if ..... then => 
   ...............
   ..... default ... then =>

   // and so one

I struggle to start off. How can I create a database schema for this -- for the steps and dependencies between them?

Improve this question
5
  • 4
    Sounds like what you are looking for is schemas for "workflow". You might try searching for that term.
    – GrandmasterB
    Commented Aug 8, 2018 at 4:40
  • 3
    Usually you not starting with database schema, starts with your application logic. If part of the logic is saving current state and retrieving it later - implement it in memory first. Create an object with all data you want to persist and use this object to "restore" application state. You can serialize or deserialize that object (Json, Xml for example) for now. Later when you move further you will see what kind of database you want to use based on the structure of your object.
    – Fabio
    Commented Aug 8, 2018 at 10:50
  • I'd employ a state machine or the state pattern. And for every possible state, I would model the data required and design the necessary scheme for it to persist that data into the db. Edit: You may have one starting point, ~10 states to which you can transition in various ways. And each 10 states hold different data about the state itself. So then I there would be 10 tables for them. I would then probably also create a state transition / history table to be able to know which path each User took, etc.
    – Aphton
    Commented Oct 7, 2018 at 16:07
  • 1
    The first step, I would say, is to define the repeatable pattern. You're currently showing use a blob of ifs (which imo isn't particularly readable) - can you break this down to its recurring core constituent? As an analogy, your question currently contains a LEGO sculpture; can you instead provide an accurate definition of what a LEGO block is? (That definition is going to be essential if you want to be able to efficiently store this information)
    – Flater
    Commented Oct 8, 2018 at 7:16
  • What happens in "..."? Is it user input or arbitrary code execution or something else?
    – JacquesB
    Commented Jan 5, 2019 at 12:21

5 Answers 5

Reset to default
1

You can implement a state machine in the database by saving each step and its links

eg:

table state
    id
    name

table link
    id
    stateId -- start
    nextStateId -- agreed_to_T&Cs
    question -- do you agree to the T&Cs
    condition -- A = yes

The problem really comes in coding up the questions. You have to dynamically build the UI for the different types, drops down, checkbox, text, number etc and evaluate the condition against the answer and possibly other state.

Its not impossible, but you end up basically inventing a new language.

I've done it before with a XML state document, which you build up with all your answers, and XPath conditions. Which you could evaluate against the state doc and get a true/false result.

But I wouldn't recommend the approach. It's easier in the long run to hardcode the logic and simply edit and recompile when changes are needed.

Improve this answer
1

The database should be used for storing thresholds and configurations that will be used by decision making logic in the code e.g. "User income is greater than X, suggest our Premium product" X will be in the DB, but the code that checks if User.Income > X should be in your C#/Java/other type strong language. For that you don't need anything other than tables like

Create Table IntSettings(
    name varchar(100) not null,
    value int not null
).

This "type strong" table declaration avoids misunderstandings like a fellow programmer trying to insert "$1000000" for a price setting because they feel they need to give the currency for whatever reason. This misunderstanding could cause some unexpected part of the code to explode when parsing the setting.

Another thing you can store in your DB is question/option text and their translations. It would be like the other settings table, except with columns for the country/region.

The database should not be encoding things like what questions the user is asked, which screen to go to next etc. because they have their own place in your architecture.

The only advantage of doing this decision making in your schema is that you can make ad-hoc changes in response to bugs. But you are also more likely to make mistakes doing everything in SQL.

If you find yourself needing to change the UI rapidly (and unable to wait for a code release) you should look at improving your deployment pipeline and also asking if you have a good enough picture of the UI design, business logic and requirements.

Improve this answer
0

If the number of choices is small, you can try to utilize a decision table. This is a table of all boolean combinations of the choices your users can make. For example, for 3 choices, it may look like this:

choice 1|choice 2|choice 3|action
--------+--------+--------+-------
False   |False   |False   |DoFoo1
False   |False   |True    |DoFoo2
False   |True    |False   |DoFoo3
False   |True    |True    |DoFoo4
True    |False   |False   |DoFoo5
...

Of course, in reality the table may look different, choices may not just be "true" or "false", and maybe lots of possible combinations won't occur (which would result in a sparse table).

Saving such a decision table in a database is not particular hard. You will probably need to have the number of possible choices to be extendable at run time. So you could use a DB schema with 3 tables (ChoiceType for the n possible choices, Action for the possible actions, and the Actual choices in a link table (maybe called Choice) between Action and ChoiceType).

Of course, this solution will only work for a certain degree of complexity, if the dependencies between the different choices and the actions ("user experiences") become too complex, something different like a state machine (as mentioned in the comments) may be better suited.

Improve this answer
0

You already have the basics down. You probably need en entity for "step" and an entity for "condition", where a condition links one step to another. How this more concretely is represented is impossible to say without more details about what the steps and conditions actually represent in your code. E.g. are the conditions user input or code expressions? What happens in the steps? Is the decision tree executed in one go, or do you need to store state as in 'user X is currently at step B' in the database? What happens at the end of the brach?

If we assume each "condition" is just a yes/no question presented for the user, then it is pretty easy. 

Step
====
Id (PK)

Condition
=========
Question (text)
Priority (number)
ForStepId (FK to Step)
ToStepId (FK to Step)

If the user is at step X, they are presented with the first question with ForStepID = X. If the answer is yes, go to the step indicated with ToStepId. Otherwise present the next question for step X.

Improve this answer
0

I think JaccquesB is almost there.

Start drawing your states and transitions on paper, states being nodes and steps (=transitions) being lines connecting them. Write conditions besides the step lines. Note a step has a direction, there may be two different steps connecting any two states. A step has a from and a to state.

So you have users, states, steps and conditions. This is your model:

User <<---> State 2--->> Step <--->> Condition

The conditions may not be all fixed and may also depend on some user properties. That would be up to your app logic to handle.

Improve this answer

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