I think I see what you're getting at . . . . I would first use a bit more descriptive field names, even in your database. This really helps when you've been at it for hours and getting fuzzy-headed. :-) There are probably more efficient ways, but this is how I would approach it. The short story: you should always structure your database so that suppemental values can expand and contract as you need them - examples below.
create table user_symptoms (id int(11) primary key auto_increment, user_id int(11), [your other fields, don't know what they mean], symptom int(11), symptom_descr tinyint(2), is_delayed tinyint(1));
- symptom = numeric id of "309.81 Posttraumatic Stress Disorder" - more details below
- symptom_descr = chronic/acute, see below
- is_delayed = "With Delayed Onset" - see below
- I left off table creation options and indices for simplicity, you may or may not want options and indices depending on how these are used ( symptom_descr tinyint(2), index(symptom_descr) )
- I used tinyint when boolean or enum might work as well. Why? What if, for example, you decide to add another option to acute/chronic, like acute/chronic/other? You'd have to modify the database. In this scenario, 1 could represent acute, 2 chronic, if you ever had to add "other", that could be 3. The same is true of the other fields - is_delayed could be a boolean yes/no, but you might add "not sure". The first mistake you can make is to assume that that will never happen, I've made it often. :-)
- I left off the "code" criteria because those appear to be multiple options that may or may not be present/selected (and am not sure how they relate here, so using it as a "multiple checkbox" exercise.)
So given our base table looks like this,
You would have a series of other tables on which to join to get actual data:
1|309.81 Posttraumatic Stress Disorder
... we can tell that the user has selected 309.81 Posttraumatic Stress Disorder (symptom id 1 from symptoms), it's Acute (id 1 from symptom_descriptions), and has not indicated it's a delayed onset. Which brings us to what may be "checkboxes" in the form, a series of options that may or may not be present.
1|1|1|Duration of the disturbance is more than 1 month.
2|1|3|The person has been exposed to a traumatic event in which both of the following were present: <ul><li>Person experienced, witnessed, or was confronted with an event or events that involved actual or threatened death or serious injury, or a threat to the physical integrity of self or others.</li> <li>Person's response involved intense fear, helplessness, or horror.</li></ul>
3|1|2|The disturbance causes clinically significant distress or impairment in social, occupational, or other important areas of functioning.
The person has been exposed to a traumatic event in which both of the following were present-
4|1|4|In children - this may be expressed instead by disorganized or agitated behavior.
This allows you to attach these criteria as multiple entries, but only to the symptom with the ID of 1 via the symptom_id, "309.81 Posttraumatic Stress Disorder". Looking forward, maybe this table will have a lot of other "symptoms" and you could specify the criteria for those as well. Also it follows the above idea that it is easily expandable. You could add "The person has served in military combat" (redundant, but you get the idea.)
The sequence field is a field by which you can re-arrange the order in which they display, which gets important in data like this. If you look closely, it does not match the entry of the auto increment id's.
select description from ptsd_criteria where id=1 order by sequence asc;
Why would you create multiple tables, and use numeric values when you're itching to enter a textual value for "Acute" or "Chronic"? Numeric fields are always going to be faster, and in the interest of normalization, you only store words like "Acute" or "Chronic" once, and in one place. You would join on these supplemental tables by id to get their textual values.
This would bring us to one last table for your user's data:
We'd be able to tell that this user (user_id 1) only selected "Duration of the disturbance is more than 1 month." (id 1) and "In children - this may be expressed instead by disorganized or agitated behavior." (id 4) as the criteria via checkboxes in any forms you have.
A side note, you could also do this in programming, but I wouldn't do it that way. It would still be fairly easy to update, but what this does is mix the programming layer with the database layer and they will forever be intertwined. I'm only posting it here because it may clarify to you what I'm thinking. A PHP example,
$symptom_descriptions = Array('Acute','Chronic','Other');
$symptoms = Array (
'309.81 Posttraumatic Stress Disorder',