How do I Find Best Match of Disease from Symptoms

[sani kolik]

I have three tables.

I have the following database table with information about symptoms, diseases, and diseaseSymptoms:

DISEASE_T   SYMPTOMS    diseaseSymptoms
==========  ==========  ========
DISEASE_ID  SYMPTOM_ID  DISEASE_ID
VALUE       VALUE       SYMPTOM_ID

I want to search user selected symptoms, which are array of symptoms id in diseaseSymptoms table and return top 5 matching disease IDs , based on, where most of the symptoms are matching with which disease.

For example, user select symptoms are 1,2,3 (cough,cold,headache)

DISEASE_T  SYMPTOMS_T  diseaseSymptoms
=========  ==========  ===============
1,fever    1,cough          1,1
2,flu      2,cold           1,2
3,xyz      3,headache       2,2
                            2,3
                            3,1
                            3,2
                            3,3

Now, i want to find disease ids from disaseSymptoms table, where all these three of idz matches, ranked as 1, where symptoms matched are less than Rank 1, like only two symptoms match, ranked as 2 and so on.

which approach you people suggests for doing so?

it might be a noob question to ask but i am a newbie in database development, still learning. any help will be appreciated.

Regards

[NeoPa]

You would design a query in SQL which filters only those records from [diseaseSymptoms] that match your specified symptoms and then groups the results by disease. The results would include the ID of the disease as well as the count of records in the group. The count would be the field you order by in descending order for the data you need.

[NeoPa]

In fact, the SQL would be something like :

SELECT   [DISEASE_ID]
       , COUNT(*) AS [Rank]
FROM     [diseaseSymptoms]
WHERE    ([SYMPTOM_ID] IN(X, Y, Z))
GROUP BY [DISEASE_ID]
ORDER BY [Rank] DESC

X, Y & Z are the symptoms you'd like to match against.

[Rabbit]

What if disease A has 10 symptoms but matches all 3 while disease B has 3 symptoms but only matches 2.

[NeoPa]

According to the question, disease A has the higher score of 3 compared to 2 for disease B. As far as I can see this is also reflected in the example SQL shown.

[Rabbit]

I was just playing devil's advocate and pointing out a situation in which it may be preferable to account for the total number of symptoms in relation to the number of matched symptoms.

[NeoPa]

Probably worth throwing into the mix, but would certainly complicate the SQL if taken up, that's for sure.

[Rabbit]

Well, if I was a patient being diagnosed by this database, I wouldn't mind complicated SQL lol.