3 Replies Latest reply on Jun 6, 2014 12:57 PM by erolst

    One or Two Inventory Tables

    tbcomputerguy

      Hi there, we are starting to develope a purchasing database and I am a little confused about setting up the product pricing. I have suppled a partial of the inventory we have. starting at row 5 these are accessories. the top 4 are main products. I have thought about lookups but can't get my head around it with the current formats. Then i thought if I filter out the products based on product number and category then do the lookup that would work. Again, I then thought that I should set up two inventory tables one for main products with a lookup for 5 price levels and one table for accessories. Not entirely sure which direction to go. I believe that the inventory should be in one table...i could use an if / case formula to lookup the price. see lots of ideas.

       

      Thanks in advance

      Dave

        • 1. Re: One or Two Inventory Tables
          erolst

          You either store a product price directly with the related product – or you setup a PriceLevels table related to Products with prices applicable at different points in time and/or for specific suppliers and/or levels.

           

          This allows you to lookup the correct price for a line item simply by “looking into” the matching PriceLevels record, based on the product foreign key and e.g. the supplier foreign key from the order (or their level).

           

          The product category is an attribute of a product and shouldn't have any impact on the lookup method; in that regard, a product should be just a product, not a “main product” or an “accessory”.

           

          Using different Inventory tables and determining the applicable price via Case() gets you on a slippery slope where you will be constantly required to make re-adjustments. If you introduce a new level, or a new category, you must add new table(s) and/or tweak those calculations.

           

          Using the correct data model OTOH simply works®, and will even allow non-developers to add new levels or categories, since they are encoded as data, not buried in the schema.

          • 2. Re: One or Two Inventory Tables
            tbcomputerguy

            The prices are determined by the quantity purchased.  So someone who purchases and accessory falls into first, third and fifth levels only. and conversely, the main products fall into the first, second, forth, sixth and seventh levels.  If you look some of the price levels overlap, like 6-49 falls into the 6-99 level one is the main products like siding and the other is an accessory.

            • 3. Re: One or Two Inventory Tables
              erolst

              You can translate this list from your screenshot directly into a ProductsPrices table related to Products, holding the price, the min and the max quantity – and AFAICT, ignoring the type of a product and any level altogether.

               

              Then create a relationship between LineItems and ProductsPrices where

               

              LineItems::_fk_productID = ProductsPrices::_fk_productID

              LineItems::quantity ≥ ProductsPrices::minQuantity

              LineItems::quantity ≤ ProductsPrices::maxQuantity