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

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?

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?

You can use an Identity or a GUID, and that decision depends on other factors.