homepage Welcome to WebmasterWorld Guest from 54.166.53.169
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Pubcon Platinum Sponsor 2014
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

    
Creating a table
kenny724



 
Msg#: 4393492 posted 5:40 pm on Dec 2, 2011 (gmt 0)

Trying to learn how to create tables...i have 6 columns. type_cd, item_cd, code_desc, code_desc2, code_desc3, valid_index. I need to add info like this:

[] 309.81 Posttraumatic Stress Disorder

Specify:
[] Acute: if duration of symptoms is less than 3 months
[] Chronic: if duration of symptoms is 3 months or more
Specify:
[] With Delayed Onset: if onset of symptoms is at least 6 months after the stressor


Posttraumatic Stress Disorder core criteria:
[] Duration of the disturbance is more than 1 month.
[] 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-
[] 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.
[] Person's response involved intense fear, helplessness, or horror. [] In children- this may be expressed instead by disorganized or agitated behavior.


[] 309.81 Posttraumatic Stress Disorder would be type_cd 0..and the item_cd 1.....then the three choices acute, chronic, delated would be type_cd 1 and items_cd 1,2,3 right? Would the criteria also have the same format...or do I make different numbers all together?

 

tangor

WebmasterWorld Senior Member tangor us a WebmasterWorld Top Contributor of All Time 5+ Year Member Top Contributors Of The Month



 
Msg#: 4393492 posted 11:50 pm on Dec 2, 2011 (gmt 0)

@kenny724: Must admit I'm a little confused, too. Is this a form which inputs to the table? Is this the output from the table? A little more information would be helpful. That said, we don't write turnkey code for free, but we will help with any best effort example you've made and troubleshoot any errors.

In any case, use example.com for code which might include your website url.

rocknbil

WebmasterWorld Senior Member rocknbil us a WebmasterWorld Top Contributor of All Time 5+ Year Member



 
Msg#: 4393492 posted 5:51 pm on Dec 5, 2011 (gmt 0)

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

Some notes:
- 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,

id|user_id|symptom|symptom_descr|is_delayed
1|1|1|2|0

You would have a series of other tables on which to join to get actual data:

table symptoms
id|title
1|309.81 Posttraumatic Stress Disorder
2|1234.24 Influenza
3|45.34 Hypotherma

table symptom_descriptions
id|descr
1|Acute
2|Chronic

(or other!)

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

table ptsd_criteria
id|symptom_id|sequence|description
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:

user_ptsd
id|user_id|criteria_id
1|1|2
1|1|4

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',
'1234.24 Influenza',
'45.34 Hypothermia'
);

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved