AnsweredAssumed Answered

Help with database design

Question asked by marcel_1 on Jun 14, 2010
Latest reply on Jun 21, 2010 by FentonJones


Help with database design



I am trying to make a fairly complicated database and need some advice. I am developing a tool for medical education, and want to have a database that relates;

1) diseases

2) causative agents

3) physical signs

4) symptoms

5) laboratory findings

6) radiographic findings

7) labs

8) associated syndromes


I am developing the database to provide an alternative to learning the material in a flat sense by just reading a textbook, so I would like to have a relational database that allows the user to for example view all conditions that are associated with a certain lab value, or see all causative agents linked to a certain radiographic finding. My approach so far is to have a table for each of the 8 items with appropriate relationships between diseases and each of the other 7, and show related fields using portals etc. 


So far so good; but now I have encountered some problems that i don't know how to deal with;

1. Often a condition will be linked to certain findings that are very infrequent. So let's say that disease #1 causes deafness in 1% of individuals, and in disease #2, deafness is almost universal. When I look at symptoms from the perspective of either disease #1 or disease #2, I can see "deafness" as a linked symptom, but have no sense of the frequency unless I can add some kind of a modifier that is record-specific from the perspective of diseases. Is there a way to do this?


2. Diseases can have symptoms individually, and might also be associated with syndromes, or collections of signs/symptoms/findings that occur together. It would be great to show this information and be able to search for all diseases that have "cough", say (regardless of whether it is an individual symptom or part of the syndrome "flu-like illness"). I have no idea how to structure this without running into circularity problems or creating duplicate tables for symptoms, signs, findings etc. associated with syndromes.   


Any thoughts on these problems (or general suggestions for the database) would be greatly appreciated!