Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2007

    Unanswered: How does my Inventory database look?


    MODS - I'm not sure if this should go into this topic or "database concepts and design", please move if it should be there instead.

    I'm trying to create a database that will track orders for new inventory that we have here at our shop. I'm trying to make it so we can input new orders and look up old orders and their related products. What I was thinking of doing is creating one master input form where the data can be put into when a new product is ordered. I would then have a query to lookup the data after it has been input.

    Attached is the relationship report, please let me know what you think of it.

    I haven't worked in Access in a looong time so if this is way off please let me know, thank you so much for your help!
    Attached Thumbnails Attached Thumbnails Relationships4.1.08.JPG  

  2. #2
    Join Date
    Nov 2007
    Adelaide, South Australia
    Ummm... This will probably be moved, but I'll chuck in my first impressions here.

    Why not just have the checkin fields in the Order table? Looks like a 1:1 relationship there. Same for VendorOrderInfo. Don't think either of these tables are needed really.

    Shipto, ShipVia, ShippedFrom all look to be related and designed incorrectly. These should have primary keys that are ShipToID, ShipViaID and ShippedFromID respectively. They should be related to order in the same way as the Employee and Vendor are.

    Product should be related to OrderDetails, not Order. With that design, you can only have one product per order. You need to relate them like this:


    And Order Details needs to have both ProductID and OrderID in it and those two fields make up the primary key for order details.

    Hope that helps
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!

    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  3. #3
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    frankly its a reasonable start, but i thgink you have some serious issues to resolve
    why do you have an order detail table.. all of the detail there is a one to one realtionship, so it can quite happily reside int he orders table
    your model only supports a single product per order.

    classically UI'd expecgt an order header contiasning heaverything (eg order date, delivery date, po nuymber etc
    an order detaisl table which identifies what product has been ordered, the quantity and the agreed price (if relevant)

    I don't understand the vendor table and manufacturer table (unless this is to cater for you buying from several wholesalers the same range of products form different manufacturers.

    I would probably expect some form of interesection table to identify which wholesalers / vendors stock which manufacturers products (you may even need togo further and identifyu which manufacturers products are sticked by which wholesalers, as not all wholesalers stock the whole range from each manufacturer

Posting Permissions

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