Educational disease database

[austplow]

My aim is to create a searchable database that can display tables with
similar values (eg. all the things that cause watery eyes; or all the things
that cause watery eyes AND red eyes AND are treated by drug 'X').
You can see an example of a record in flow chart form at:
(http://members.westnet.com.au/austplow/diseases database.gif). My data
types will appear mainly as three word fields with a few paragraph types.

Subsequently to a search, I hope to display the similar data in tables.

Is my intended database suited to MS Access or not?

Would hand coding in XML be appropriate?

How can I make the database searchable by multiple keywords?

Any assistance would be greatly appreciated,
Kind Regards,
Leigh

 

[Allen Browne]

It is certainly possible to do this kind of thing in Access, but it will
need a normalized data structure to give you the searchability you need.

One disease has many symptoms.
One symptom can apply to many diseases.

This many-to-many relation is resolved using 3 tables:

Disease table (one record for each disease):
DiseaseID Unique ID
...

Symptom table (one record for each symptom):
SymptomID Unique ID

Disease (a record for each combination):
DiseaseID
SymptomID
So, if a disease has 10 symptoms, there are 10 records in this table that
have the DiseaseID.

In a similar way, there is a many-to-many relation between disease and drug.

You may also want to associate many keywords with different
diseases/symptoms/drugs/whatever, so there are further relations. Sounds
like you have further descriptive text (and possibly links) associated with
these entities.

The core aspect of making this searchable is to get the right data
structure. This is called "normalization", and is the first step towards
building any database. It is the design phase, like drawing up the
blue-prints for building a house, and the usefulness of the entire database
relies on getting the right structure, with the right mix of rigidity
(locking out bad data) and flexibility (coping with unforseen issues.)

 

[austplow]

Thank you very much for your assistance, Mr. Browne!
As you suggest, I plan to diagram my database structure and then implement it.
As it will be an ongoing database, it is important to adopt a well-rounded
framework.
I hope to begin planning the database soon, and will let you know how it goes.
Kind Regards,
Leigh