Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2014

    Question Need help with Inventory Database Design

    We are currently creating an inventory checkout system for a class project, but can't figure out how to allow customers to checkout individual parts of an item(currently items have accessories [parts] that can be checked out with main item). Currently our database is set up like this:

    Customer (CusID PK)
    --------< Checkout (CusID PK FK, ItemID PK FK, Date PK)
    > ---------- Item (ItemID PK)
    ----------< Part (PartID PK, ItemID FK)

    Currently the checkout system doesn't account for what parts are being checked out or for multiple parts to be checked out with an item. Can anybody help? Thanks

  2. #2
    Join Date
    May 2009
    Item may have more than 1 part.
    The part may also be sold "loose".
    The loose part (accessory) may be common to more than 1 item.
    The cost of an item sold as a single piece may be less than the cost of accessories (parts) sold individually. It is essentially a different product when sold as a single item.

    So I would have
    (1) A bill of material for these items that are sold as single units and also as accessories.
    (2) In the inventory data base, I would HAVE SEPARATE masters (and so separate transactions) for the group.

    When checked out, I would then be able to deplete the corresponding part number.

    Take a toy set consisting of 4 bats and 2 balls wrapped in plastic bubble sheet and sold as a unit. Mostly you would check it out as a unit. BUT kids will loose the balls and buy balls alone. So you have 3 items in the inventory master.
    (1) Bundled toy (4xbats+2xballs).
    (2) Bats
    (3) Balls

    And the bill of material file for that may will have 3 items:
    (1) Bats with noff=4,
    (2) Balls with noff=2
    (3) Disposable wrapping.

    By having separate codes for the unit and accessories, together with a simple bill of materials, you can get any type of analysis while keeping the checkout and inventorying process simple.

    Last edited by AnanthaP; 12-12-14 at 05:11.

Posting Permissions

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