Unanswered: Problems creating a database to rent equipment
I'm having issues creating a database to track equipment rentals.
Attached is the relationships of the different tables. The database should be able to rent equipment to an employee so he can perform work on an activity. There are two types of equipment that the employee can rent, a vehicle and a piece of small equipment. I'm having trouble having a vehicle and small equipment being logged under the same equipment ID field.
I would REALLY appreciate some input as to how it looks thus far. It's been awhile since ive worked with Access and I am probably missing something pretty obvious! Thanks so much for you help!
This is the area where im having problems. I want each piece of equipment to have a unique equipment ID. I wanted this unique equipment ID to be able to be rented out to an employee. There are 2 types of equipment so i assumed i needed two tables to log the different types of equipment separately. I have a feeling i need to just create a query to bring these two tables together to create a complete equipment ID list, but then im running into the problem of trying to tie these two tables into the equipment rentals table somehow, im confused as to how to do this. That's why there is the other equipment table. Probably doesn't need to be there!
So to answer your question, I have both the vehicle equipment ID and the small equipment equipment ID set to autonumber, and then the equipment ID within the equipment table set to number. This doesn't make sense and it doesn't work, but i'm at a loss as to how to do it other wise.....
I haven't started putting data into this database yet because of the problems with the equipment. Any recommendations or changes please let me know, it's still in the design phase. And thanks for your help!
Note to moderator - I think this should be moved to the database concept and design topic
One issue you may come across with this design is giving 2 pieces of equipment the same ID - they may be in different tables but I think it may cause some problems. Also, calling both fields euipmentID will confuse the heck outta people so why not have a smallequipID and an equipID?
in fact taking another look at this - why do you need the equipment table at all? The type can be found by looking at which table the data has come from!
Remove the equipment table and relate to the employee equip rental.
I am by no means an expert on db design and this is definitely a puzzler! These are simply my initial impressions / passing comments. I'm sure some people will disagree - it's up to you to wade through the ideas and come up with your improved design
Keep us updated with your ER diagrams as you go along!
I am going to add my two cents because I have had this exact same problem and I would like to know if there are any other ideas.
The problem you have is you have varying types of equipment that have varying attributes or columns. What I would suggest (because this is how I did it) is to have one Equipment table. Put all of your equipment in it (both Vehicles and Small). Then add a second table with Vehicle details (EquipID, vin, vyear, etc.) The Vehicle Details will only have some of your EquipIDs in it. I would also recommend an EquipmentType table (Small and Vehicle) you may need to add more later.
Then consilidate your Equipment and Small Equipment tables into one Equipment table. The table would have EquipID, EquipTypeID, Condition, Description, etc.)