Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2010
    Posts
    22

    Any advice for a simple inventory database?

    Here at work, we have an "inventory" system using an Access database to track what employees are utilizing what items. Problem is, it is horrible. Like it's not just horrible, it's hilariously god awful.

    So the idea is to make a new inventory system with a web interface, and integrate it into our internal web service.

    The thing is, I feel like I may have oversimplified it or something, I don't know, it was just way too easy to come up with the design. Any advice on this following 6-table build?

    ---

    1. Users
    - unique ID, name, and description (i.e. Bob, IT manager)

    2. Locations
    - unique ID, description (i.e. Room 103)

    3. ItemTypes
    - unique ID, description (i.e. Furniture)

    4. ItemBrands
    - unique ID, description (i.e. Sony)

    5. Items
    - itemID, locationID, itemTypeID, itemBrandID, serial number, descripion, startdate, enddate, comments

    6. ItemUsers (intersection table to find out who is using what item, since multiple users can use the same item)
    - itemID, userID

    ---

    And that's it. That covers everything the crap Access inventory does, and more actually. I can't see at all why this wouldn't work. But it only took like 2 minutes to draw this out on paper, so I'm second guessing myself, like surely it couldn't have been that easy. Any suggestions for such an inventory system where the sole goal is to track internally what employees are using what items?

  2. #2
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    I would probably have an Items table containing information specific to the item itself (and not the location.) ItemID, ItemType, ItemBrandID (Manufacturer), Description, S/N

    Add an ItemLocation table, which is an intersection between Item and Location, with the start and end dates as well. ItemID, LocationID, StartDate, EndDate. Possibly comments.

    And the ItemUsers table would be the intersection between item and user. Potentially, this table could also have start/end dates, to define the time range that a user has control of the item.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


Posting Permissions

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