Staff vs. Contact Tables

[Kelly]

I'm pretty sure I have a design problem here but can't
seem to see it (although I'm sure it's right in front of
my face!)...
I have a database that stores library information
(location, employees, etc). In this database I've got
three tables that are starting to frustrate me: one is a
table that contains library information, one contains
staff members, and the last contains the people whom are
certain contacts for each library. These tables are
called Library, Staff and Contacts. The library table has
a primary key called library code. Library code is a
foreign key in the staff table (indicating where the staff
person works primarily) and is also a foreign key in the
contacts table (indicating which library they are a
contact for).
My problem is this: when I try to run a query that pulls
library information, staff information (those that work at
the library) and contact information (the contact people
for that library), I end up losing some data (usually the
library code field for staff or contacts). How can I
design this better? Contact people are staff members that
work at a single library but can be contacts for many
libraries. There are also about 5 different contact types
(I use a field called contact type in the contact table).
Any help would be appreciated!!!
Thanks,
Kelly

 

[Jeff Boyce]

Kelly

You kinda "snuck" a little zinger in there near the end. It sounded like
you said that a "contact" was a "staff" member?! If that's the case,
they're ALL staff members, right?

It sounds like you have Libraries and People, and you have a "Role" (this
person is a "staff" type at that library; person B is a "contact" type at
library 17, ...)

How "welded" are you to your current data structure?

 

[Kelly]

Not Welded at all!!
It is correct that all contacts are staff members. The
part I'm having trouble with is that I need to distinguish
who works where and who is a contact for each library.
So, even though a Jane Doe is a staff member at Library A
she is also a System Administrator (contact type) for
Library A as well as Library B, C and maybe Z. I
eventually want to create a report that lists each
library's information, the contacts for each library (type
and name, ie. Sys Admin: Jane Doe), and the staff members
at each library (people who work there but are not
contacts of any sort). Does that all make sense?
Thanks!!!

 

[Jeff Boyce]

Kelly

It sounds like Jane Doe could have more than one row in the "Role" table I
suggested earlier. One row would be:

"Jane Doe" "works at" "Library A" "as a staff member"
"Jane Doe" "serves" "Library A" "as a System Admin"
"Jane Doe" "serves" "Library B" "as a System Admin"

If I were setting up a table to handle this, I'd be using ID#s, so it would
look more like:

23 1 4 7
23 2 4 10
23 2 5 10

but I'd be able to use a query to join the four other tables and "get" the
text back.

Here's the structure I've come up with, based on what I understand so far:

tblPerson
PersonID
FName
...

tblFacility (in case the "business" is ever broader than "Libraries")
FacilityID
FacilityName
...

tlkpRelationship (a table of lookup values: "works at", "serves", ???)
RelationshipID
Relationship
...

tlkpPosition
PositionID
Position (e.g., staff member, Administrator, ???)
...

trelRole
RoleID
PersonID
RelationshipID
FacilityID
PositionID
BeginDate
EndDate
...

Does this come close to describing your situation?