Are all keys uniquely labeled? (You've indicated that some keys have a particular label format...)
Sounds like you need a table of keys, one record for each key and a table of people one record for each person to which a key might be checked out. The question next to answer is what besides tracking who has each key do you need to track about each key.
Do you need to keep track of when it is checked out?
Is there an expected date when the key is expected to be returned or are keys checked out for an indefinite length of time?
Do you need to track the history of who had a particular key in the past?
If each key has a unique identifier and you set up one record for each key and all you need to track is who currently has the key, you can do it with a simple one to many relationship between people and keys:
People::__pkPeopleID = Keys::_fkPeopleID
If you need to track more info, then you may need a third table for recording that information.
For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained
I'm sorry, there are up to 10 copies of each key (serial # - 1, serial # - 2, serial # - 3, etc) and multiple people may have the same key that is issued to them for an indefinite length of time.
Thank you for the quick reply I'm checking out the link you posted now.
You may want an additional table where you have one record for each key serial number linked to the table I called "keys" in my earlier example. But this may or may not be necessary, it depends on what all you need to do with this information.
Am I at least headed in the right direction?
It would be better to set up a primary key field in Keys and use it as the match field to a single foreign key field in People. That makes for a simpler relationship that is easier to manage.
Also, your current relationship assumes that every key only opens doors in a single building. If that's always the case, no problem. But if you have any master keys that open doors in more than one building, what you set up won't work--so that might be a second reason for using the fields that I have recommended.
Note also that if you need to track which keys open doors in a particular building, there may be a need for an additional table or two to record data on each building a single time for linking to the keys that open doors in them.
For similar reasons, you might even need a table of rooms, but all of this depends on what all you need to do with your database besides keep track of who has a given key.