Table design for complex decision tree

[Seth Schrock]

I need help figuring out how to design the table structure for a complex decision tree. It is complex because some questions require two (or more) selections to make the answer true. The result of the decision tree is a change of wording in a court report. My idea is that a crime would be selected in a combo box. The crime would have some text already typed in. The decision tree would then just put the specifics into the final wording. Here is an example. Indiana code IC 35-45-1-3 Disorderly Conduct Indiana code IC 35-45-1-3 Disorderly Conduct

Did the subject recklessly, knowingly or intentionally:
  1. Engage in fighting or tumultuous conduct
  2. Make unreasonable noise and continued to do so after being asked to stop
  3. Disrupt a lawfull assembly of persons

If one of the above (more than one could be selected) is committed, then the person will be charged with Disorderly conduct, a Class B misdemeanor. If none of the above is selected, then the crime wasn't committed.

The next set of questions needs asked as long as one of the above questions is answered as Yes. However, it doesn't matter which one, so there is no real tie for the next set of questions to any one of the above group.

Did the prviously described offense:
  1. Adversely affect airport security, and
  2. Was committed in an airport or on the premises of an airport...

If both 1 and 2 are selected as true, then the crime becomes a Class D felony.

The next set of questions will then be asked no matter what the answer was to the second group.

Was the offense committed within 500 feet of:
  1. The location a burial is being performed, or
  2. A funeral procession if the subject knew that a funeral procession was taking place, or
  3. A building in which is being conducted:
     a. A funeral or memorial service
     b. the viewing of a deceased person
  4. ...and the offense adversely affected the funeral, burial, viewing, funeral procession, or memorial service

Either a or b has to be true for 3 to be true, and one of 1, 2, or 3 with number 4 also being true for this group to be true. If it is, then the crime becomes a Class D felony (coincidence that it matches the crime of group 2).

Another tricky part is that the selections that you make changes the final wording. So lets say in the first group, number 3 was selected. It then goes to group 2. Nothing gets selected as true and it goes to group 3. Letter b gets selected as well as number 4. With these options selected, then the final wording that comes out of this is:

Disorderly Conduct, IC 35-45-1-3 Class D Felony
Seth Schrock did recklessly, knowingly, or intentionally disrupted a lawfull assembly of persons within 500 feet of a building in which was being conducted the viewing of a deceased person and the offense adversely affected the viewing, contrary to section 35-45-1-3 of the Indiana Code and against the peace and dignity of the State of Indiana.

Hopefully this question and my requirements make sense. Here is another link to a similar crime which not only changes the severity of the crime (Class A, B, C, or D Misdemeanor or Felony), but also changes the wording: Indiana Code 35-42-2-1: Battery

What I have tried**************************

I already have a table for the penal codes: tblPenalCodes. It has the following fields:

PenalCodeID, PK
PenalCode, text (sample data IC 35-45-1-3)
Title, text (Disorderly Conduct)
LegalWording, memo (the part of the wording that doesn't change)
BaseCrimeSeverity, number, related to a crime severity table) 

I then have made a table for different types of questions. For example, you have Crime Defining questions (like the first group), Question type of reqular questions, and a Group type for when you have multiple questions under it, like in group 3 you have a and b. Table structure is: tblQuestionTypes

TypeID, PK
Type, text

I then have a table for the requirement type. Or, And, and Multi are the types that I have. Group 1 are all Or because you just have to have one of them. Group 2 are And because they are both required. Group 3 has questions 1, 2, and 3 as multi because one of the is required in combination with number 4 (an And) to make it true. a and b in group 3 would be Or.
tblRequirements

RequirementID, PK
Requirement, text

For the questions, I have:

tblDecisionTree
QuestionID, PK
PenalCodeID_fk, number, related to tblPenalCodes
TypeID_fk, number, related to tblQuestionTypes
Question, text, what is seen in the decision tree
NewCrimeSeverity, number, related to tblCrimeSeverity
ParentID_fk, number, allows a question to be a child of another question
QuestionLevel, number, would allow me to group questions together based on their level.  Not sure if needed.

This table would be just for the storage of the decision tree questions. I would have another table that would be related to this one that would store which ones were selected for a particular incident.

I have worked on this for about a month and I think that my brain as turned to mush making it very hard for me to think clearly without getting confused. I have two main problems which are not allowing me to figure out the table design and usage. 1. Making it so that in use, making a selection will change the final wording, and 2. making it change the crime severity (in the above example, from the base level of Class B Misdemeanor to Class D Felony).

Right now, all I'm looking for is the table design and maybe some raw data to show how things are linked. Once I have that, I think that I can come up with the forms and code necessary to use the data. If not, I will ask in a separate thread (possibly linking to this one).

I had asked before about how to create a decision tree and had opted to use a tree view control. At the time, I wasn't aware of all of the requirements for the decision tree. This will teach me to ask more and better questions about what is required when trying to make design decisions.

[TheSmileyCoder]

If it is any consolation at all, I will admit to having no good ideas for a table design to accommodate your requirements, and consider it a understandably complex task. That said, I find the challenge fascinating, and will try to think about it, on and off and see if anything pops up.

Best of luck with your project.

[Seth Schrock]

I'm glad you didn't say "oh, that is simple". At least there is one expert that has to think about it :). Hopefully you or another expert can come up with one eventually though. This just isn't remotely near anything that I have done before.

[zmbd]

Without any intent to offend: this is why flow charts were designed in the first place.

>> I wouldn't attempt any table design until you can get the basic flow chart made! Doing so results, in most cases, brain melt. Melted brain is hard to remove from most fabrics and impossible to remove from the keyboard and most wood surfaces.<<

I prefer the NS system, even though they are more for programing than general decision work: 56 Nassi-Shneiderman charts and then there's this: A short history of structured flowcharts (Nassi-Shneiderman Diagrams)

There are IMHO a few nice things here: only a few basic symbols to master, and at each action block, "sub-blocks", you can simply place a reference to another decision tree and then work that out, or list instructions and so forth.

Code is easily written within the structure and there is some level of "object" level code inherent in the method. However, keep in mind that object level code was just coming onto the stage when I learned this method.

You can ofcourse use the older flow-chart methods and there maybe new methods out there too.

Best of luck

[Seth Schrock]

Didn't know flow charts could be used for designing tables. I have a few times used them for the design of the database so that I don't leave anything out. I will look into your link and hopefully I will come up with something.

Thanks Z, and no offence taken :)

[ADezii]

I have come across a similar challenge in that certain decisions need to be made based on prior questions. If it is just the Final Outcome of the Decision Tree Results that you need, without the need for Data Storage, then I can show you in a very small way how I attacked the problem since I am pressed for time. It is difficult to explain the concept, so I'll simply attach a small Demo:

[Seth Schrock]

It would at least be a start. It might give me an idea.

[zmbd]

I should have noted that the charts will help you decide as to what tables you'll need and how to group them...

When I've done things like this (and I have not taken a look at ADezii's example yet) is to either within the same table or as separate tables used "reference fields" that allow the classification questions and where to goto next (I also do this same branching as I've referred you to in the past using the biologist's nomenclature tree structure)....

but a short example:

For a very simple yes-true/no-false

"tbl_tierone"
[tierone_pk] - Autonumber
[tierone_question] - Question to ask the user
[tierone_truetable] - table to refer to in a yes/true response
[tierone_truegroupcode] - questions to filter out in the table
[tierone_trueaction] - action/code to take if yes/true
[tierone_falsetable] - as above but for no/false
[tierone_falsegroupcode] - as above except for false
[tierone_falseaction] - as above except for false
[tierone_groupcode] - how to group this question within this table

When the answers are not so simple; I've taken this even further by using a related child table when more than one option is available (instead of yes/no - a,b,c,d...) removing the true/false fields from tbl_tierone

tbl_tieronechild
[tieronechild_pk] autonumber
[tieronechild_fk_tierone]
[tieronechild_tieroneselection]
[tieronechild_selectiontable]
[tieronechild_selectiongroupcode]
[tieronechild_selectionaction]

So now there is an entry in this table for each option allowed in the parent table (also useful for quize questions and is now the way I tend to do this as this tends to fit better under the normalization rules - had a tierone that the user swore to me would never have anything except y/n questions... I should have known better(!) wasn't three months in production and they wanted a 3 part selection).

Notice, the by using the "selectiontable" the choice that the user makes can point them back into the tbl_tierone and a new group of questions... perhaps even a special form etc to call... or you could use that to drop to tbl_tierfinal and take the questions there.


However, there are times with even trying to deciede what and where to start is not straight forward; thus, by using that NS chart I can start grouping (tier) and classing (groupcode) questions and answers, and if needed any actions, leading to my final query/report.

[TheSmileyCoder]

How many penal codes are we talking about? I presume its more then a handful. If its was just a handful I would probably hardcode each of them, but I am guessing its more then that (Actually a lot more then that).



Could you provide some details about the general purpose of the application, as well as the process flow leading up to this entry form, and what the desired output is, from the end users perspective. I often find the more details I have, the easier it is to get the mind running.

[Seth Schrock]

Well, this is going to be part of the Fill-in-the-blank database that you helped me with before Smiley. For those of you who don't know what I'm talking about, this database is for Police Officers when they charge a person with a crime(s). They have to fill out a report for the court stating exactly what happened. Some penal codes have multiple options. The fill-in-the-blank part was to make it so that all the officer had to do was to type in the accused person's name, the complainant's name, and any other information required and it would then concatenate all the parts together so that they didn't look like it was just blanks filled in. Currently, for the options, the officer just circles whichever option was the part committed. The officer that I'm doing this for (my brother) doesn't want to have to circle anything anymore. That is where this decision tree comes into play. The decision tree will affect both the final wording for the court report and the crime severity in some cases.

And yes Smiley, there will be several (not sure exact number) penal codes. I also want to be able to offer this to other departments which would have their own wording (the wording is up to the local prosecuter), so I don't want to have to hard code this.

I will look at your sample ADezii and also at your tables Z, but right now I have to go to an all day training at work. Hopefully tonight I will be able to study your ideas. Thanks.

[Rabbit]

Here's a preliminary design I came up with. It doesn't account for multiple replacement texts but that's easily handled by splitting it into a different table. It also doesn't account for subsections like your 3a/b split but I was able to refactor those into the section above. The pass and move threshholds are handled as bitmasks to determine whether or not they pass that level of questioning, whether to move on to the next level of questioning, and which text to replace.

Crime

CrimeID   CrimeName            CrimeSection   CrimeText
1         Disorderly Conduct   IC 35-45-1-3   {Crime}, {Section} Class {Class} {Perp} did recklessly, knowingly, or intentionally {L1} {L3header} {L3}, contrary to section {Section} of the Indiana Code and against the peace and dignity of the State of Indiana

QuestionLevel

LevelID   CrimeID   Level   PassThreshhold   MoveThreshhold   Variable     ReplaceText           Severity        LevelDesc
1         1         1       1                1                                                   B Misdemeanor    Did the subject recklessly, knowingly or intentionally
2         1         2       3                0                                                   D Felony         Did the previously described offense
3         1         3       17               0                {L3header}   within 500 feet of    D Felony         Was the offense committed within 500 feet of

Question

QID   CrimeID   Level   QOrder   OrAmount   QuestionText                                                                                                 Variable   ReplaceText
1     1         1       1        1          Engage in fighting or tumultous conduct                                                                      {L1}       engage in fighting or tumultous conduct
2     1         1       2        2          Make unreasonable noise and continued to do so after being asked to stop                                     {L1}       make unreasonable noise and continued to do so after being asked to stop
3     1         1       3        4          Disrupt a lawfull assembly of persons                                                                        {L1}       disrupt a lawfull assembly of persons
4     1         2       1        1          Adversely affect airport security, and                                                                                  
5     1         2       2        2          Was committed in an aiport or on the premises of an airport                                                             
6     1         3       1        1          The location of a burial is being performed, or                                                              {L3}       the location of a burial being performed and the offense adversely affected the burial
7     1         3       2        2          A funeral procession if the subject knew that a funeral procession was taking place, or                      {L3}       a funeral procession that the subject knew was taking place and the offense adversely affected the funeral procession
8     1         3       3        4          A building in which is being conducted a funeral or memorial service, or                                     {L3}       a building in which was being conducted a funeral or memorial service and the offense adversely affected the viewing
9     1         3       4        8          A building in which is being conducted the viewing of a deceased person                                      {L3}       a building in which was being conducted the viewing of a deceased person and the offense adversely affected the viewing
10    1         3       5        16         … and the offense adversely affected the funeral, burial, viewing, funeral procession, or memorial service              

[Seth Schrock]

@ Rabbit I think I just figured out what you are doing with the PassThreshHold and MoveThreshHold. Let me make sure of that though. For MoveThreshHold, you are stating the value of all the OrAmounts added up required to move to the next level. In this case, since the value is 1, any of the OrAmounts selected would be greater than or equal to 1 and so making a selection would allow the questions to advance to the next level. Not selecting one would make the added value to be 0 which is less than the required 1.

The PassThreshHold is again tests the value of all of the selected OrAmounts added up and requires it to be greater than or equal to the PassThreshHold value. So in the case of the level 3 questions, adding up the values of the first 4 only comes up with 15 and not the required 17. Just selecting number 5 would add up to 16 and again not the required 17. This means that number 5 plus the addition of any one of the first four would add up to greater than or equal to the required 17. I take it that all of the And requirements would need to be place at the end of the question list? I haven't tested any math on that to see what would happen to the values, but I can do that easily enough.

[Seth Schrock]

@Rabbit One quick question, why the CrimeID and the Level fields in the question table? I assume you would create the relationships to both tables from tblQuestion.

[Rabbit]

@Seth, in regards to post #12, that's almost exactly correct. The only difference would be that I was thinking of using a bitwise OR operation instead of an addition operation. The reasoning being that it allows you to bitwise OR the same amount for multiple questions without affecting the final total whereas that option does not exist for addition. It allows for easier grouping of questions and determining their value. For example, if you have 4 questions and a pass is calculated with ((1 or 2) and (3 or 4)), you can assign a value of 1 to question 1 and question 2 and a value of 2 to question 3 and question 4. The pass value for this scenario would be set to 3.

As for post #13. Technically it's not needed, you could and should use LevelID instead in the question table. I just did it that way because I find it easier to understand the data when the foreign key represents something a bit more concrete.

[Seth Schrock]

I think that will work Rabbit. Thanks for your help.

For the bitwise OR comparison, I have only ever done bitwise comparisons once and I didn't design the code that used it (I think NeoPa did) so I'm not totally sure how to use it. Do you have a website that would help me learn it? I can then ask any specific questions about it in another thread.

[Rabbit]

The bitwise operators don't work in the SQL engine so you'll have to write a VBA wrapper to use in SQL. But the usage of the operators is fairly simple.

1 OR 2 = 3
1 AND 3 = 1
1 XOR 1 = 0

For more detailed explanations on what's happening with the operators, you can read the wikipedia article. http://en.wikipedia.org/wiki/Bitwise_operation

[NeoPa]

Rabbit:
The bitwise operators don't work in the SQL engine ...

So the AND & OR operators always return a boolean value of TRUE (-1) or FALSE (0) regardless of the numerical values they link?

It seems that way from my testing, but I was certainly surprised to see it.

[Rabbit]

That's correct. I haven't figured out a way to get the SQL enginer to recognize that I want to use bitwise operations as opposed to boolean operations so I've resorted to using a VBA wrapper.