|
There is a concept in database models called a role.
A role is,
well, the role, played by a participant in a relationship.
It often comes up with people. We may have only one
employees table, yet employees may play several roles:
worker, inspector, technical lead, etc. What if we have a
table with columns for both inspector and worker? The
Microsoft Relationship Designer can't represent this concept
the way a more traditional modeling tool such as Visio
would. But it can be done.
Example Database
Schema
We will use an expanded version of the
Hospital Admissions student database for an example. You can download the MS Access database (and others) at
box.com.
The schema can be represented in text with a few
conventions: bold = required field,
underline = primary key, italic
= foreign key.
Patients(PatNo, fName,
lName, BirthDate, address, city, state,
zip)
Diagnostics(DiagNo,
descr, cost)
Doctors(PhysId,
fname, lname, specialty)
Admissions(AdmitNo,
PatNo,
Diag_Code, Admit_Date,
Dischg_Date,
CoPay, Admitting_Phys,
Referring_Phys)
Database
Design
The database design has each one of the above
entities with a twist. The Doctors table appears twice. When
you add it a second time Access gives it the name Doctors_1.
Next time it would be Doctors_2 and so on.
We placed the Doctors table in the
relationship designer once for each role that a doctor
plays. A doctor could be the referring (say family)
physician and another doctor the admitting physician. The
same individual doctor may not play both roles but the table
will contain doctors playing both roles. If we didn't do it
this way we would have separate tables for every role
doctors played: consulting, admitting, referring, assisting,
etc.
Queries
Let's take a
look at queries involving roles. As you can guess, there are
a few nuances that need to be taken into account. We'll do
it the wrong way first. Then the right way. This will also
clarify the concept of roles.
Wrong
In this first query we'll simply link the
admitting and referring physician foreign keys to the
doctors table and see what we get. This is the default join
generated when you add the admissions and doctors tables to
the query designer.
As shown
below the above query retrieves nothing. How can an
admissions record match a doctors record that has two
primary key values at the same time? It can't. We need to
design this query differently as shown on the next page.
Continue to the correct
roles query (page2)...
Submit the form below to be notified of new articles or other
Microsoft Access resources made available...
Learn more about our Microsoft Access courses..
|
