8 Replies Latest reply on Jun 21, 2010 7:11 PM by FentonJones

    Help with database design



      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!







        • 1. Re: Help with database design

          Could I stress that I'm pretty new to databases myself but I can try and answer part 1 of your question.


          You could create another table called say "DiseaseSympton" with fields DiseaseID, SymptomID (or whatever key field names you feel is appropriate (just make sure each one is unique!)) and also a field that holds "Frequency". On your relationships graph make a relationship  between the  "Disease" table and "Symptom" table. For each record in the "DiseaseSymptom"  table you could have a field that holds the frequency of the symptom occurrence in terms of percentage. The 'DiseaseSympton' table is a join table (thanks Phil, forgot those terms).




          Edited: `My' post was slightly incoherent and not using correct termininology. Please Phil/Fenton if I'm causing more confusion, tell me to be quiet! Still learning :smileywink: 

          • 2. Re: Help with database design


            I have been working on a database design that was some what complicated. I had to re-write it seven times to get it right. I would challenge you to put it on paper or in a flow chart. start by looking at the body first. Maybe start with the sick person like you would in the docs office. So how are you feeling, and add them in to your persons data from their. " real life stuff "


            I hope this help!


            Rich Larrabee

            • 3. Re: Help with database design

              The tables you mentioned are all what I call "reference" tables. They contain data, they do not contain linked pairs. What you needis a "join" table between any two which you want to link. For example:


              A Disease is sometimes caused by a "causative agent". Other diseases may also be caused by the same agent. Hence "Causative Agents" is not the place for the linked pairs. You need a DiseaseAgents "join" table. It would have a link to each, Disease ID and Agent ID fields. (Much as the other poster said about the Symptoms, though I'd leave that for last.)


              The join tables are where you would put % or "priority" or whatever you use to describe how often/important, etc. that particular join is. Whatever you use for that must be consistent (within that join table), so it can be sorted (may require a separate calculation field, to put things in the correct order). That way you can show the most important in a portal.* 


              You would be wise to use primary IDs in each of your reference tables, and use those in the join tables, not names. Because then the database will be much smaller and faster (which will matter), more accurate, and you'll be able to adjust for name changes, typos, etc..


              * The portals to show the links will be based on the join tables. The names fields will go thru the join table to see the name in the other "parent" table. 


              The join tables will have a lot of records, but each will have only a few fields, IDs, and the data particular to that join (the sorting field). Names and other info for each parent will remain in the parents' tables.

              • 4. Re: Help with database design

                Question 1:

                The short answer is "yes", but the application details depend on how you design your layout and tables. It may be as simple as adding the field from a related table to your layout or even a row in a portal.


                Question 2:

                This sounds like a classic case for a "join" table where you would link many symptoms (and/or many different illnesses) to many different records in a "syndrome" table where you would put one record for each such syndrome.




                Symptoms::SymptomID = Symptom_Syndrome::SymptomID

                Syndrome::SyndromeID = Symptom_Syndrome::SyndromeID


                A portal to Symptom_Syndrome placed on a symptoms layout can be used to list all syndromes that list that symptom.

                A portal to Symptom_Syndrome placed on a syndrome layout could be used to list all symptoms for that syndrome.


                Here's demo file on this technique: http://www.4shared.com/file/PLhjErzu/Contracts_to_Companies.html


                It links many different Contracts to many different companies, but the concept is the same.

                • 5. Re: Help with database design

                  Thank you for the several helpful replies. I have a few questions - please excuse the beginner-level nature of these;


                  1. "You need a DiseaseAgents "join" table. It would have a link to each, Disease ID and Agent ID fields."
                  So, I keep the 8 reference tables (for example Diseases, with records disease1, disease2 and disease3, and Agents, with agent1, agent2 and agent3), create join table DiseaseAgents with records having fields "disease" and "agent", and link DiseaseAgents to the tables Diseases and Agents using these fields. This means I have a record in the join table for each pairing - that is, if disease1 can be caused by 3 agents, I would have 3 records in table DiseaseAgents. Is that correct?


                  2. In my database, there are also pairings for symptoms, labs etc... For example, disease1 may have symptom1, symptom2 and symptom3. So for the other pairings, do I need to make another join table, say "DiseaseSymptoms"? In this case I would have 7!  (=5040) join tables, one for each possible pairing between reference tables. This seems a bit unmanageable, and hopefully I am missing something. 


                  3. Ultimately, I would like to be able to scan the database in some table or other to see linkings in any way I like. So, the user should be able to look at a causative agent and see all linked radiographic findings for that agent, or all labs linked to that agent, or look at a radiographic finding and see all linked symptoms, and all linked diseases. I gather from the comments and PhilModJunk's example (downloaded and examined, thank you!) that this will involve portals - in a given ref table, do I put in 7 portals (one to each of the other 7 ref tables)?   


                  4. What are primary IDs?


                  Again, sorry for such basic questions. 
                  • 6. Re: Help with database design


                    marcel wrote:

                    In this case I would have 7!  (=5040) join tables, one for each possible pairing between reference tables.

                    Not really, because not all tables are mutually related in a many-to-many relationship*. Based on your description, you would need "only" 7 join tables - between Diseases and each of the "signs" tables.

                    I think you should also consider  - at least for starters - putting all signs of disease (causative agents, physical signs, symptoms, laboratory findings, radiographic findings, labs and associated syndromes) into a single "Signs" table, with a field for type. Then you would need only one join table.

                    The rest depends on how different these types are in terms of their attributes. You may need to add a "satellite" table for each type, with a one-to-one relationship to the central Signs table. This is known as the supertype/subtype model.

                    However, this might not work if the join attributes are significantly different for each type (e.g. frequency for one, level for another, etc.).

                    Anyway you look at it, this is quite an ambitious project (esp. the syndrome part) - not something I would recommend to a beginner.





                    (*) Even if you had a many-to-many between all your 8 tables, that would make only 28 joins; you multiplied instead of adding.


                    • 7. Re: Help with database design


                      Thank you again for the helpful comments. I now have a DB with join tables as suggested; for example, I have a reference table with diseases linked by a join table to a reference table with causes, using primary id keys, much as in the example you provided. Minor question; how can I insert a portal in diseases that i) lets me create new records in the join table and ii) be able to see not just the related cause primary id key (a number) but the actual cause name field in the linked record in the causes table?(This will obviously be important for entering new links between diseases and causes from the diseases table.) I can see that this is the case with the example you provided, but I am not able to recreate this or see any options that permit this when I click on the portal in layout mode and examine field behavior, etc...




                      • 8. Re: Help with database design

                        I wrote a reply earlier, but Safari decided to Quit and take it with it; I was doing some editing; darn; the cloud can be a cold gray place. 


                        So, rather than try and write it again, I just built it into a little file, and put it on my web site (for a while). It has 2 "parents" and a join table between them. Look at the Relationship Graph, see the settings. 


                        There is also a Value List for each of the Parent IDs (their values look the same, don't worry about it). They are used as pop-ups on the ID fields in the portals. So it looks like the "name", but is really one of the join table's local parent ID fields (1 & 2). That's the bare basics.