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.