Setting up tables and creating the "Relationship"

Ange1ia used Ask the Experts™
I'm doing a new db based on my old one.

There was a huge table with everything in it.  I'm try to break it down and create the Relationships.

I  have a "tbl.Patients" (names, address, phones and so on). I have a "tbl.Physicians" (names / address of physicians).  I have an "tbl.OutsideFacilities" (names of other facilities we use).  I will need a tbl for CityStateZip (I think I do this as a Look Up).  Then there will be something for "tbl.Appointments" and a "tbl.Clinical" that will carry things like pathology and diagnosis it it.

I need help getting at least the first Relati.still confused

I think there should be the tbl.Patients {the one} and the tbl.Physicians and tbl.OutsideFacilities should be the many because a patient can have more than one MD or Facility that we use but I can't figure out how to make that work... I know, it's fundamental, first thing you learn, but...  And after all of this is done it's gotta look nice so I need help getting started.

Thanks, Angie
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Hi Angie,

I'm glad to hear that you're planning tables and relationships before diving into the forms and reports.  The database is the foundation and you'll be glad you took the time.

First, I suggest the book Database Design for Mere Mortals by Hernandez.  It's a great starter to this subject.  Also, I suggest that you don't use periods in your table names.  tblPatient and tblPhysician would work well.  And I suggest that City, State and Zip should not all be in the same lookup table, but separate ones.  In applications we build we don't usually keep Zip in a lookup table at all.

Okay, to the relationships.  It's pretty clear that you'll need some many-to-many relationships.  These are accomplished with "joining" or "cross-referencing" tables.  For example, if a Patient can have many Physicians, and of course a Physician can have many Patients, then a table perhaps called txrfPatientPhysician is needed.  This table would have at least Patient ID and Physician ID fields, called foreign keys, that join the two main tables together.  That table can then have other fields, like a PatientPhysicianActiveFlag, PatientPhysicianStartDate, fields like that.

This is just a quick start, but you get the idea.

HainKurtSr. System Analyst

I suggest you add these tables

Patients(PtnID, fname, lname, ...)
Physicians(PhyID, fname, lname, ...)

Addresses(AddID, country, city, add1, add2, zip,...)

AddressTypes(addType, addTypeName, decription) home, business, etc.

PeopleAddress(addID, addtype, persontype) persontype=0:patient, 1: physician etc...
PeoplePhones(phone, peopleid) peopleid = PtnID or PhyID, one table for both

Country(countryid, countryname, ...)
Province (provid, provname, countryid, ...)
City(cityid, Cityname, provid, ...)

if you can combine Patients & Physician it is good ;)

then we will have only one table for people data in common

People(PplID, fname, lname, sex, bdate,....)
PeopleType(ptID, TypeName) 1: patient, 2: physician etc...

so we can use this PplID in address & phone tables...

if we have more specific data for eacgh kind of person type, we can create extra tables like

PatientData(pplID, registerdate, insuranceCompanyID,....)
PhysicianData(pplid, Code, ...)

:) just a quick startup...

if you have A's that may have multiple B's, create table A(aID, ...) , B(bID, ...) , AB(aID, bID) for relations...
if you have A's with only one B, then put all data into A and create only one A table

oops, it is very difficyult to explain like this ;)
Top Expert 2010

Pleased to see you around these parts, Armen!Patrick (Excel MVP)
Thanks Patrick, happy to be here.  Since the newsgroups closed I'm spreading my time between Answers, MSDN, LinkedIn and EE.



I have Database for Mere Mortals, and Access 2003 Step by Step, and Access 2003 Bible, and Access 2003 for Dummies, and a few others from classes.  I am well set with books.  
Hi Angie,
Excellent set of books.  I will point out that the only thing I disagree with Michael Hernandez on is using plural naming for tables.  In our shop we use singular names (tblPatient) instead of plural (tblPatients) - it's shorter, more consistent, and reads more naturally in queries and code (IMO).  But that's a style decision - I recommend that at least you stay consistent.

HainKurt recommended subclassing your tables, with tblPerson, with one-to-one subclass tables of tblPatient and tblPhysician.  That's a good idea, but it does make your database structure and programming a bit more complex, so you'll need to decide whether it's worth it.  The main thing is to get your one-to-many and many-to-many relationships figured out.



This is how I see it lay out
strPtID          AutoNumber   (I am implementing this becasue we may phase out the Social, maybe not)
strLname     Text
strFname     Text
dtmDIB         Date/Time
strSocialSec Text  (We do use this for everyone, currently it's our key)(I guess PtID will be the new key)
strAddress  Text
strCity          Text  (currently we have this field in the pt table but I'm thinking it should be in a look up)  strState        Text  (same here)
strZip            Text  (same here)
strHomePh    Text
strWkPh        Text
strCell           Text

tblPhysician      (this is acutally a table that MS set up and it's used as a look up I guess to auto populate
                          the Phycians field when we type in the last name in a combo box)
strPhysicianID   Autonumber    

tblOutsideFacilities    (is exactly the same as Physicians table, MS made that one too & it works the        
                                   same as far as auto populates the Outside Facilities field when we type in it)

How do I hook these up?  

And oh there is so much more.  I am tempted to copy over a blank database from the years before but I don't really want to do that... I want this one to Better, Bigger, Stronger than it was before... no wait, that's the Six Million Dollar Man.  But same principal, right?  


Hi Angie,

I wouldn't prefix the fields in your tables by type (e.g. strLName).  Most developers don't.

Good idea to use an autonumber primary key, not an SSN.  You can use an Autonumber for all your primary keys if you like, with the possible exception of the StateID, which could be the State abbreviation (e.g. "WA", "OR", "CA") if you want.

I'm not sure what your question is about "hooking these up".  You've got a structure started.  You need to relate all the primary keys and foreign keys together in the Relationships window.  For example:


StateID  <---  this is your foreign key related to the StateID in tblState



Hi Armen

By "hooking up" I  mean relating them.

I used the prefix because when I first went to class that's what they taught us to do, use Lezenski Tags I think they were called.  But if it's ok not to, that's easier.  

So... I feel like the Patient table is my main "Parent" table.  But one patient can have several MDs out the the Physicians table.  They may have a Referring MD as well as a Primary Care Physician.  And a physician can refer more than one patient.  So that sounds like a many to many relationship to me.

One patient can have several Outside Facilities (I request radiology films and people go multiple places sometimes)  And one facility I guess would essentially have more than one patient, again a many to many. I was always taught this was a very undesirable relationship to have.

If I use the Primary Key from one table to become the Foreign Key from another table how would be the best way to set those relationships up.

Patient Table
PatientID          Primary Key
PhysicianID     Foreign Key
OutsideFacilit  Foreign Key

Or would it be    

Physician Table
PhysicianID      Primary Key
PatientID           Foreign Key

Outside Facility Table
OutsideFacilityID    Primary Key
PatientID                 Foreign Key

I'm not sure how best to do that.  And this doesn't even include the tables I am going to have to pull out of the original mega table I have that will include appointments, pathology, clinical stuff.  This is db is so important.  I really appreciate your guidance.  I have the "Mere Mortals" book with me tonight and have been looking thru it.  I have a difficult time reading and learning things, I'm more of a visual learner, Attention Deficit Disorder I guess.  

If I use the other tables as a look up table do they need relationships?  I guess that's best?  But I don't think all of my current tables have relationships but they have to in order to do qrys, right?


Take a look at this Word.doc where I tried to create relationships.
Hi Angie,

First, the Primary Key is an Autonumber.  Its matching Foreign Key in the other table is a Number (Long Integer).  This is correct.

Next, if Access shows you a one-to-one relationship, it means that the fields on both sides are unique.  This could be because they are a primary key, or because you've specified a unique index.  If this isn't what you want, then you need to remove the unique index on the field from "many" or "child" side.


Unique Index? Do you mean that I need to change something in the properties box in Design view of the Foregin Key.

Yes, if the Index property of the Foreign Key says "Indexed (no duplicates)", then it's unique on that side.  You need to change it to "No".


I still have some questions but this will get me started.  Thanks

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial