locked
Need help on Entity-Relationship Design RRS feed

  • Question

  • I need to design a schema that will provide me a hierarchical view in reporting. I mean by this is, lets say:

     

    Hospital

         |_________> Hospital Entity

         |                      |         |

         |                      |        V

         |                      |         Clinic A

         |                      |            - Jane Doe MD

         |                      |            - Janette Brown MD

         |                      |         ACME Clinic

         |                      |            - Jennifer Smith MD

         |                      |         Clinic of Jennifer Smith MD

         |                      |            - Jennifer Smith MD

         |                      |            - Billy Johnson MD

         |                      |   

         |                     V

         |                      - Earl Brown MD (Hospital Entity Resident Practitioner)

         |                      - Janette Brown MD

        V

       - John Smith MD

       - Bob Jones MD

     

     

    The design should also allow me to query a practitioner and the query will show all the entities he or she is a member of.

     

    If this is not the correct place to post this posting, please let me know the correct place and I will make the necessary changes.

     

    Thanks,

     

    Stephen

    Tuesday, July 3, 2007 2:22 PM

Answers

  • There are several ways you can do this, depending on how the data will be used. You can go as far as a full "Normalization" exercise:

    http://www.informit.com/guides/content.asp?g=sqlserver&seqNum=60

     

    Or for a more simple application you can just use a recursive function to keep all this data in one table.

    http://www.sqlteam.com/article/more-trees-hierarchies-in-sql

     

    It all depends on whether the entities need more details than just the relationships you described.

     

    Tuesday, July 3, 2007 7:55 PM
  • Buck,

     

    Thank you so much. This is very helpful. I see that I have some reading to do.

     

    I created the database design using VisoModeler. It would be nice if I can pass the design and have others comment or critic my work.

     

    For those that are interested to see the design and have time to comment or critic my work, please send me an email at scantoria@heops.com and I will send it to you. I can send the design in pdf or .iml file for VisioModeler users.

     

    Thank you again.

     

     Stephen Cantoria

     

    Tuesday, July 3, 2007 9:20 PM

All replies

  • I'm not sure exactly how to read your diagram. How is the Hospital and the Hospital Entity different?

     

    Is this what you are trying to do?

     

    Hospital has one or more doctors associated with it.

    Hospital has one or more clinics associated with it.

    Clinic has one or more doctors associated with it.

     

    If the above is correct, then I would recommend tables as follows:

     

    Hospital table

    Clinic table

    Hospital-Clinic Join table that lists the Hospital ID and then Clinic ID of each clinic associated with the hospital

    Doctor table

    Hospital-Doctor Join table that lists the Hospital ID and then Doctor ID of each associated doctor

    Clinic-Doctor Join table that lists the ClinicID and then Doctor ID of each associated doctor

     

    (Or if Clinic and hospital have similar data, you could put them both in one table with a flag defining which is a hospital and which is a clinic.)

     

    Hope this helps.

    Tuesday, July 3, 2007 3:05 PM
  •  

    An Organization has zero or many Sub-Org in it

    An Organization has zero or many People in it.

    A Person can be a member of one or many Organizations.

     

    Would the table relationship you described work with the requirements above?

     

    Thanks,

     

    Steve

    Tuesday, July 3, 2007 3:16 PM
  • Yes...
    Tuesday, July 3, 2007 4:39 PM
  • There are several ways you can do this, depending on how the data will be used. You can go as far as a full "Normalization" exercise:

    http://www.informit.com/guides/content.asp?g=sqlserver&seqNum=60

     

    Or for a more simple application you can just use a recursive function to keep all this data in one table.

    http://www.sqlteam.com/article/more-trees-hierarchies-in-sql

     

    It all depends on whether the entities need more details than just the relationships you described.

     

    Tuesday, July 3, 2007 7:55 PM
  • Buck,

     

    Thank you so much. This is very helpful. I see that I have some reading to do.

     

    I created the database design using VisoModeler. It would be nice if I can pass the design and have others comment or critic my work.

     

    For those that are interested to see the design and have time to comment or critic my work, please send me an email at scantoria@heops.com and I will send it to you. I can send the design in pdf or .iml file for VisioModeler users.

     

    Thank you again.

     

     Stephen Cantoria

     

    Tuesday, July 3, 2007 9:20 PM