Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2007
    Posts
    4

    Unanswered: Problems creating a database to rent equipment

    Hello,

    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!
    Attached Thumbnails Attached Thumbnails equiprelationships.GIF  

  2. #2
    Join Date
    May 2005
    Posts
    1,191
    How are the vehicle and small equipment ID's generated? Are they assigned, or are they set to autonumber incrementally? For that matter, how are new items added to the lists?
    Me.Geek = True

  3. #3
    Join Date
    Jun 2007
    Posts
    4
    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!

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    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!
    George
    Home | Blog

  5. #5
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    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.)

  6. #6
    Join Date
    Jun 2007
    Posts
    4
    thank you both for your replies.

    I'm going to try and use both of you're recommendations tonight, see what works best, and will let you know how things went tomorrow. Again, THANKS!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •