4 Replies Latest reply on May 17, 2010 11:19 AM by philmodjunk

    Product Database Best Practices

    ThomasShaw

      Title

      Product Database Best Practices

      Post

      To all,

       

      I'm looking to build a product database that will supply information to a purchase order database.  I recall reading somewhere something about product pricing and keeping track of product prices for historical purposes instead of changing the price each time the same product is ordered over and over.

       

      Are my thoughts on the right track?  Please share what best practices you all have come up with regarding a setup like this.

       

      Many thanks in advance.

        • 1. Re: Product Database Best Practices
          aammondd

          I would think about it like this

           

          How often does the information about that product change and can that information be tied to some other type of record

          Information that can be tied to some other kind of record inventory purchase order invoicing should be kept in its own record

          Even pictures and descriptions can change periodically and should be given consideration to the "effective dating"

           

          Just think in terms of one to many relationships if there is a many relationship about this kind of information then it should more than likely be its own table.

           

          Also consider time when determining if there is a many relationship if the information changes frequently over time or needs a point in time reference then you more than likely need a table for  that.

           

          In  the case of descriptions and pictures sometimes its a new product ID to accommodate things but even this can be avoided with effective dated tables for changeable information.

           

          Its good to really look at your data structure and plan these things out well effective dating is a real good way to deal with things.

           

          When you build a relationship to an effective dated table you match on key fields and  effective date sorted descending. This allows you to place a single related field on a layout and it always be the currently effective date. You can also add portals or other schemes to show history

          Dated records are joined by their dates in such a way to produce the correct the historical relationship (ie effdt < invoice date)

           

           

           

           

          Price is definitely something that can change often 

           

           

          • 2. Re: Product Database Best Practices
            philmodjunk

            Since this is a Purchase Order database. You don't want to see the current effective price for every item listed in the purchase order, you want to see the price that was effective at the time the purchase was made. Thus ongoing price changes should not appear in past purchase orders.

             

            In filemaker, you have a built in tool that specifically serves this purpose. You can set up a unit price field that uses the Looked Up value field option to copy the current effective price from your pricing database at the time you enter/select a product ID. With looked up values, ongoing price changes in the pricing table do not automatically update the values previously entered in your Purchase Order's line item table.

            • 3. Re: Product Database Best Practices
              aammondd

              By combining both you can look up a price based on the date of the purchase order rather than the current effective price.

               

              Sometimes we either catchup on work or plan ahead in which case we want the price from the right date and we aren't the one in control of the price entries

               

              • 4. Re: Product Database Best Practices
                philmodjunk

                Thanks for clarifying aammondd,

                 

                That's exactly what I had in mind. :smileywink: