database - MySQL query to get all (additional) symptoms and diseases -

i have database contains main symptoms, additional symptoms , diseases. need replace 2 queries because sure first query not efficient , 2nd not correct @ all. hope can me because new area..

database explanation:

the database being used medical app:

1. the user selects specific bodypart
2. the app lists main symptoms of specific bodypart
3. the user selects main symptom (common or less common)

4. the app lists diseases of selected main symptom. there appear 2 checkboxes (additional symptoms) can checked user. order of listed diseases (d_weight) depends on age, gender, selected main symptom , boxes user has checked. disease d_weight <= 5 considered common disease. disease d_weight > 5 considered less-common. possibilities of user input (age, gender, bodypart, main symptom) stored in symptom_disease_combi table.

   • asa_id id of symptom apply (addtional symptoms checked user)
   • asc_id id of possibilities of additional symptoms belong specific main symptom. example, asc_id = 0, in case there no additional symptom selected. asc_id = 1, in case additional symptom 'insomnia' selected. asc_id = 2, in case both 'insomnia' , 'blowing up' selected.

1. query symptoms of specific bodypart (can improved):

select distinct s.name               , s.id               , sdc.s_common             symptom s               , symptom_disease_combi sdc            sdc.age = ".$age."              , sdc.gender = ".$gender."              , sdc.bodypart = ".$bodypart."              , sdc.s_id = s.id 

2. query diseases , additional symptoms of selected symptom (doesn't work):

select distinct d.name               , d.id               , sdc.d_weight               , adls.id               , adls.name             disease d                , symptom_disease_combi sdc               , symptom s               , adlsymptom adls            sdc.age = ".$age."              , sdc.gender = ".$gender."              , sdc.bodypart = ".$bodypart."              , s.id = ".$sid."              , sdc.s_id = s.id 

3. database structure (please let me know if design can improved)

create table symptom (id int not null auto increment ,name varchar(100) default null ,critical int not null ,primary key (id) ) engine=myisam;  id name                    critical  1 behavioral disturbances        1  2 ear pressure                   0  3 elevated temperature           0  4 fainting                       0  5 head pain                      0  create table disease ( id int(11) not null auto_increment, name varchar(100) default null, critical int(11) not null, description text not null, tests text not null, treatment text not null, primary key (id) ) engine=myisam auto_increment=19 default charset=latin1  id name                critical    description    tests       treatment  1 adjustment disorder 0           lorem ipsum    lorem ipsum lorem ipsum  2 adhd                0           lorem ipsum    lorem ipsum lorem ipsum  3 drug reaction       0           lorem ipsum    lorem ipsum lorem ipsum  4 seizure (epilepsy)  1           lorem ipsum    lorem ipsum lorem ipsum          create table adlsymptom ( id int(11) not null auto_increment, name varchar(100) not null, primary key (id) ) engine=myisam auto_increment=5 default charset=latin1  id name  1 insomnia  2 blowing  3 depressed  4 drug abuse  create table adlsymptom_apply ( id int(11) not null, as_id int(11) not null, primary key (id,as_id), key fk_additional_symptom_that_apply (as_id) ) engine=myisam default charset=latin1  id as_id  1 1  1 2  create table adlsymptom_combi ( id int(11) not null, as_id int(11) not null, primary key (id,as_id), key fk_additional_symptom_combination (as_id) ) engine=myisam default charset=latin1  id as_id  1 1  2 2  3 1  3 2  create table symptom_disease_combi ( id int(11) not null auto_increment, age int(11) not null, gender int(11) not null, bodypart int(11) not null, s_id int(11) not null, s_common int(11) not null, asc_id int(11) not null, asa_id int(11) not null, d_id int(11) not null, d_weight int(11) not null, primary key (id), key fk_symptom (s_id), key fk_additional_symptom_combination (asc_id), key fk_additional_symptom_that_apply (asa_id), key fk_disease (d_id) ) engine=myisam auto_increment=65 default charset=latin1  id age gender bodypart s_id s_common asc_id asa_id d_id d_weight  1 1   1      1        1    1        0      1      1    1  2 1   1      1        1    1        0      1      2    2    3 1   1      1        1    1        0      1      3    3  4 1   1      1        1    1        0      1      11   4 

update 1:

• critical in disease , symptom created tell user need go hospital immediately, when click on disease or symptom
• age, gender, bodypart id's, age = 1, means 0-5, age = 2 means 6-17, age = 3 means 18-59 , age = 4 means 60+.
• please take @ design of application, lot understand design of database: http://i.imgur.com/p9qp1av.png btw, in design; cause == disease...
• asa_id refers id of adlsymptom_apply
• asc_id referst id of adlsymptom_combi
• the 'distinct' used symptoms/diseases 1 time. sure it's not needed, don't know how fix it.

ditch 2 symptom tables, go 1 (symptom) , 1 intersect table (sdc)

i not add new column symptom, such status/level trying jack symptom in importance main or secondary, despite temptation, because make not flexible.

for instance, fainting may seem main once disease/condition skew in general

go generality, therefore 1 table symptoms, correctly have d_weight in sdc

i sdc.d_weight concept.

for instance, fainting might have weight epilepsy, not flu. whole concept screwed when flu prescribed zarontin , not tamiflu

since sdc.d_weight concept, wonder why went additional symptoms table

in table sdc, have keys/indexes names starting "fk_". have actual fk constrainst

and not naming conventions make think have them (fk's, don't have them)

for instance, real fk's bodypart, symptom, , disease/condition.

as user selects symptoms, remove them gui's ability add symptom again search

this cuts down work , simplifies querying ditching secondary table (line 1 of wrote , suggest)

note, again, use of key (which synonym index, not foreign key constraint), creates index ...

create table symptom (     id int not null auto_increment primary key,     name varchar(100) not null, -- if can't put name it, don't have     critical int not null )engine=myisam default charset=latin1;  create table disease (     id int not null auto_increment primary key, -- don't mix , match int int(11)     name varchar(100) not null, -- if can't put name it, don't have     critical int not null,     -- etc columns, text )engine=myisam default charset=latin1; 

.

create table symptom_disease_combi (   -- a.k.a. sdc     id int not null auto_increment primary key, -- don't mix , match int int(11)     age int not null,     gender char(1) not null,    -- int(11) overkill     bodypart int not null,     s_id int not null,     s_common int not null,     asc_id int not null,     asa_id int not null,     d_id int not null,     d_weight int not null,      -- additional indexes (note pk above, there , happy)      -- note of indexes (your keys) worthless queries in question     -- may useful other queries in system     -- instance asc , asa indexes not useful won't picked     -- in relation question posed     --     -- need find proper balance of index bloat based on system needs      index idx_sdc_siddid (s_id,d_id,bodypart),  -- composite, general purpose     index idx_sdc_didsid (d_id,s_id,bodypart),  -- ditto reverse     index idx_sdc_ascid (asc_id),     index idx_sdc_asaid (asa_id),     -- put constraints here:     constraint `fk_sdc_bodypart` foreign key (bodypart) references bodypart(id),     constraint `fk_sdc_sid` foreign key (s_id) references symptom(id), -- don't mix , match int , int(11)     constraint `fk_sdc_did` foreign key (d_id) references disease(id)     -- there others, such asc asa tables ?? )engine=myisam default charset=latin1; 

i think need think long , hard age column sdc going loaded new row, 1 each age, 50 120, gender='m', prostrate cancer? basing on age=xxxxx line, maybe mean >=. or leukemia, <= ... think schema needs bit of thought

so excluding age below query

-- next query, wonder why need distinct? -- there more 1 sdc.s_common given symptom row ? know -- if not, ditch distinct

-- order of join on clause matters, make follow index results come fast

-- not suggesting these covered indexes, minimize table scan activity

select distinct s.name, s.id, sdc.s_common symptom s join symptom_disease_combi sdc on sdc.s_id=s.id , sdc.bodypart=".$bodypart." , sdc.gender= ".$gender." 

-- not comment above age -- incorporate weights needed in computed column

those suggestions, , luck