Results 1 to 4 of 4
  1. #1
    Join Date
    May 2009
    Posts
    2

    Question How to design Phone Entity to associate to multiple other entities

    Good morning everyone,

    I hope you can help me with this design question. I am designing a database to track patients, physicians, and contacts. I would like to capture multiple phone numbers for each of these entities. In the current database we use GUID's to manage relationships between Phone and each of the other entities (Physician, Contact, Patient). I would like to get away from GUID's as they seem to cause more trouble than they are worth. Currently there are no constraints on these relationships between GUID columns.

    Due to the use of GUID's there was never an issue with a Contact having the same ID as a Patient. In the new design I am considering using and Identity column of data type int. This will require that I keep track of a PhoneOwnerType (Patient, Physician, or Contact) so that I can differentiate from Contact ID 134 and Patient ID 134.

    Obviously this adds columns and a table. I'm at a loss as to which is a better design. The GUID approach is simpler with less tables and columns, but I feel like I have run into issues when I have said...darn it...I wish this weren't a GUID column.

    Any thoughts on this design or another better design for capturing multiple Phone numbers for multiple entities?

    Thanks

    mamorgan1

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    I'd load all contacts/patients/physicians/etc into a single table (which I usually call "Entities"). I'll then have a column in that table to indicate the type of Entity, or better yet have a subtable to hold Entity types, as who is to say that a physician can't also be a patient?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    May 2009
    Posts
    2
    Blindman, can I assume you prefer the use of Identity (int) to the GUID in this situation?

    What are the negatives of a super entity/sub entity design?

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    You can use an Identity or a GUID, and that decision depends on other factors.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •