'MySQL: Database design for procedure flow

It's something like knowledge-based system. For example:

When a user go to first page, let's call it "How Can I Help You With?" There will be 4 options:

  1. In case of fire
  2. Injured
  3. There is a fight
  4. Obedience

If the user selected 1 "In case of fire", it will ask them some other questions:

  1. Can you use the fire extinguisher?
  2. Is anybody hurt?

If the user selected 2 "There is a fight", it will ask them something like:

  1. Is anybody hurt?
  2. Security around?

Basically, if the user selected either 1 "In case of fire" or 2 "There is a fight", there will be at least one question in common in the procedures.

The end point is a help article. For instance, if the user selected "Is anybody hurt?", that will be the end-point, where the user sees the help document.

How do I design the database for this?

mysqldatabase-designprocedureknowledge-management


Solution 1:[1]

This is a variation of the Bill Of Materials database.

One way to design this would be the following table:

Question
--------
Question ID
Previous Question ID
Question Text
Help Article URL

The Question ID is an auto-incrementing integer, and is the primary or clustering key for the table. The Previous Question ID is null for your top level questions, and a pointer back to the Question ID for lower level questions. The Help Article URL is null or points to a help article.

The question numbers are supplied by the application as it reads rows from the Question table.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Gilbert Le Blanc

Related Questions