3 Replies Latest reply on Mar 26, 2013 11:05 AM by philmodjunk

    Table structure for inventory pricing

    PeterMontague

      Title

      Table structure for inventory pricing

      Post

           I am going to attempt to price my books. My plan is to download the source code of two pages where my books are for sale: Amazon.co.uk and also bookfinder.com. I may end up using just one of these pricing pages.

           My plan is to scrape the prices of all of the offers available on these pages. Usually the cheapest offer is displayed first. I would also like to scrape the conditions of each of these books. Sometimes books are listed as Brand New etc. 

           I envisage that I would have a table with many prices and their corresponding conditions. I would then use a calculation to decide what price to set my book at. 

           What I would like advice on, at this stage, is how to structure this, table wise. I have an inventory table. This could be connected to the price table (yet to be developed) with sku as the matching field. Each record in the inventory table could have several child records. Each child record would have, ISBN, price, condition and vendor. (Its important to list the vendor as some of them have unfeasibly high prices. Therefor its important to discart certain vendors.) Is this a good structure?

           After running the pricing script I would like to have the option of keeping my old prices or referring back to old prices. E.g. if I notice that sales were particularly good on a certain date I'd like to replace my today's prices with those of the date of the good sales. 

           A drop down list of dates for pricing dates would be handy. Or I may decide that this is not a good idea after all seeing as there is a school of thought that prices are only competitive for a very short time period. 

           One problem I have with this process is that it takes my database, which is on FileMaker Server 12, about four seconds to download a source code, parse it and run calculations on each record. So it could be a bit unfeasible to run it this way, as I can't use Filemaker Pro while a script is running. I hear that Amazon have an API for pricing. Has anyone any experience with it?

        • 1. Re: Table structure for inventory pricing
          philmodjunk

               Each record in the inventory table could have several child records. Each child record would have, ISBN, price, condition and vendor.

               Why wouldn't each of these be a different record (with same ISBN, but different serial number based sku) in the inventory table? I thought that's why we got rid of the "Child - SKU" table.

               That said, a related pricing table makes sense as you can use that table to control when a given price change goes into effect by including an effectivity date in the pricing table.

               

                    it takes my database, which is on FileMaker Server 12, about four seconds to download a source code, parse it and run calculations on each record.

               only 4 seconds? How often do you plan on running this script? Wouldn't it be a once a day script to run on all your current inventory? A script could be scheduled to pull the data from the website into a table during the (Presumably) slow early hours of the morning for you to review and modify when you choose to later in the day.

               I hear that Amazon have an API for pricing. Has anyone any experience with it?

               If there's an API that let's you formulate a URL that queries Amazon and returns back to your browser or web viewer a list of just the data that you want (or at least greatly simplified from the public web page), then this is definitely the way to go as it will, I think,  greatly reduce the parsing needed to extract the needed values.

          • 2. Re: Table structure for inventory pricing
            PeterMontague

                 I thought I would need a child table as a place to put the prices in order to make comparisons. If there was another, better, way I would be interested in trying it. Are you suggesting I use variables instead of fields to place all if the scraped prices for comparison. 

            • 3. Re: Table structure for inventory pricing
              philmodjunk

                   If you are comparing prices for the same item--either from different sites or different times, then a child table that matches by ProductID would be needed. This table would not need a field for "their corresponding conditions" as this should be a field in your inventory table. (I am assuming that by "condition" you mean values such as "New, Like New, Lightly Used, Used..." )

                   I am not suggesting anything about variables. I am suggesting that if you can use Amazon's API to query for pricing on your books, you may not need all the complex calculations for extracting (parsing) the values out of the text as the content returned from such a URL based query of their site may produce much simpler data--even a single field or 2 columns of data, the sku and the price.

                   I don't know if that is possible or not, but if it is, I think you can see the advantages to using that approach.