Database normalization

I am trying to create a database for a simple key loan and return System. Basically, a key to a specific door is loaned to somebody and then returned. So far I have 3 tables. Not sure if it should be normalized further, since only one key will be issued and returned per transaction.

tblKeys
KeyID
Location
DoorNum

tblKeyMaster
KeyMasterID
KeyMasterName

tblKeyTrans
IDKeyTrans
KeyID_in
Date_in
KeyMaster_in
KeyID_out
Date_out
KeyMaster_out

 

First, I don't understand what tblKeyMaster holds. Could you explain that. Most likely it isn't necessary.

Second, tblKeyTrans seems to have too many fields. What data does the KeyID_, Date_, and KeyMaster_ fields hold? It seems you are storing the KeyID twice per transaction which would mean somone could borrow Key #4 and return Key #7 in the same transaction.

Explain your fields and how they relate to what your overall objective is.

 

Keymaster holds the person that loans the key (KeyMaster_out) and the person who receives the key back (KeyMaster_in). KeyID should of appeared only once in the tblKeyTrans table.
The way I had originally thought of making this table was to input when the key was loaned by entering in the KeyID, Date_out, and KeyMaster_out fields. Later, when the key is returned the fields Date_in and KeyMaster_in are filled, thus closing the record.

Restructured the table..

tblKeyTrans
KeyTransID
KeyID
Date_out
KeyMaster_out
Date_in
KeyMaster_in
Notes

 

Looks good. Just to be clear though this structure allows Jeff to borrow a key and then later Sally could return it (KeyMaster_in doesn't have to equal KeyMaster_out). As long as your fine with that then the structure is good.

 

How the normalization is done and what are the advantages of that? Please help me.

 

these three tables could be represent in to more normalised and Database design could be more simple