Key Inventory - Best way to set up

[TaiChi56]

Alright a little confused. Here is what I have. I am setting a key database. It is for a school. Right now I have one table that has everything so that is not a true database.

1. Teachers

2. Keys

3. Rooms

Should I break it up to these three tables. I have over 260 rooms, so obviously a key for each room, but then I have cabinet keys and desk keys. I was thinking about making a table listing all the keys but that would be over several hundred. Would it be better with a value list? I want to be able to click a room and it displays what key belongs to it, i.e. room 206 KC1. Also want to be able to link the key to the teacher and the teacher to the room. Where do I start? Right now I have a database but it is basically a flat file.

[comment]

Do you want to track all keys, or just the ones assigned to a teacher?

[TaiChi56]

Good question. I need to track the ones assigned to the teachers. But this changes throughout the year. That is why I must have the whole inventory in the database.

[comment]

But this changes throughout the year. That is why I must have the whole inventory in the database.

I don't see the connection. Let me ask this another way: suppose there are 5 keys to a room; 3 are assigned to teachers, and 2 are locked in your cabinet. Is it enough if the solution shows something like:

Room 206:

Total keys: 5

Assigned keys:

• Adam

• Betty

• Cecil

Available keys: 2

IOW, are the keys numbered?

[TaiChi56]

Yes, that will suffice. Yes, the keys are numbered. They are numbered by the locksmith so room 223 is key KD1. That is also the problem. I need to be able to link that key KD1 is room 223 key. The only way I would know is go to a master list that was given to me by the locksmith. I would like to be able to look for room 223 and the database shows me that key KD1 is the room key that three were made and one is issued to so and so. THank you for the quick repsonse.

[comment]

the database shows me that key KD1 is the room key that three were made and one is issued

Just to make sure I get this correctly: all three keys are numbered KD1? What if you need two keys more: will the locksmith number them KD1 too?

[TaiChi56]

Yes the keys stay the same numbers.

[comment]

Well, then I believe the three tables should be sufficient - although I would rename Rooms to Assets (rooms, cabinets, etc.), and Keys to KeysIssued. The relationships are simple:

Assets -< KeysIssued >- Teachers

In the KeysIssued table have fields for:

• AssetID

• TeacherID

• DateIssued

• DateReturned

(the active assignments are those where DateReturned is empty)

Edited March 11, 2010 by Guest

[TaiChi56]

Thank you very much for your reply. I will do that.