Where to put phone numbers and addresses

G

Guest

I am developing a clinical database which includes listings of

Patients
Providers
Staff
Sites
Medical events.

Patients, Providers and Staff may have multiple phone numbers (e.g. home,
work, cell, etc.) and addresses (e.g. PatientHome, PatientWork,
ProviderSite1, ProviderSite2, etc.) They may also share the same phone
number or address (e.g. three Providers at the same Site). This potentially
makes for very cumbersome tables with many empty fields.

Does it make more sense to have a master tblPhone and tblAddress linked to
each person as a foreign key, rather than putting multiple fields into
tblPatient, tblProvider, tblStaff, and tblSites? This would simplify data
entry, table structure and the appropriate listings could be retrieved in
subforms or by queries. Greatly simplifying, this might look like:

tblPatient
Patient_PK
Patient_NameLast
Phone_FK
Address_FK

tblProvider
Provider_PK
Provider_NameLast
Phone_FK
Address_FK

tblPhones
Phone_PK
Phone_Number
Phone_Extension
Phone_NameToAskFor
Phone_Type (home/work/cell/partner/emergency/main/inside line/fax etc.)

tblAddress
Address_PK
Address_Line1
Address_Line2
Address_City
Address_Zip
Address_Type (home/work/partner/Site/admin etc.)

Thank you
 
J

John Vinson

I am developing a clinical database which includes listings of

Patients
Providers
Staff
Sites
Medical events.

Patients, Providers and Staff may have multiple phone numbers (e.g. home,
work, cell, etc.) and addresses (e.g. PatientHome, PatientWork,
ProviderSite1, ProviderSite2, etc.) They may also share the same phone
number or address (e.g. three Providers at the same Site). This potentially
makes for very cumbersome tables with many empty fields.
Click to expand...

That's why you would normalize the table instead.
Does it make more sense to have a master tblPhone and tblAddress linked to
each person as a foreign key, rather than putting multiple fields into
tblPatient, tblProvider, tblStaff, and tblSites? This would simplify data
entry, table structure and the appropriate listings could be retrieved in
subforms or by queries. Greatly simplifying, this might look like:
Exactly.

tblPatient
Patient_PK
Patient_NameLast
Phone_FK
Address_FK
Click to expand...

but you've got this BACKWARD. If you have a one (patient) to many
(phone) relationship, you would put Patient_FK in the *PHONE* table -
the foreign key always goes in the "many" side table.


John W. Vinson[MVP]
 
G

Guest

Thanks for your reply. This is my first serious database and I've been
working on big issues (such as getting table structure correct) as well as
details (such as good naming conventions). Normalizing has been emphasized
in everything I read, so I'm trying to make it "feel natural." Glad to hear
I'm on the right track.

Let me clarify two questions implicit in my original query:

1. Do I correctly interpret that I can put EVERYONE's (Patients, Providers,
Staff) address into tblAddress (since most of the fields for anyone's address
are pretty much the same) and retrieve the ones I want by its association to
a particular individual. I could use my Address_Type
(home/work/partner/Site/admin etc.) field both to sort on and to display in a
subform the options appropriate to that individual.

Similarly, everyone's phone number is formatted identically and a Phone_Type
field with options such as home/work/cell/partner/emergency/main/inside
line/fax allows me to retrieve, filter, sort and display a particular
person's specific phone number.

If that IS true, then I think I just _got_ one type of abstraction in a
database - sort of letting go of the spreadsheet mentality where things are
in a specific physical location. Rather, they is "somewhere" and and the
location is less important that the relationship by which they are found.

Of course, if it isn't true, then that's a lot of blather and please forget
it immediately.

2. Since one patient (or provider or staff) may have multiple phone numbers
and the same phone number may be shared between several providers (e.g. it's
the main phone number for the office), shouldn't the relationship actually be
through a relation table to handle the many-to-many relationship?

Thanks again for your time and tutelage.

WilDeliver
 
Top