3 Replies Latest reply on Aug 2, 2013 8:31 AM by philmodjunk

    Can Anyone Help me Think Through this Setup?

    fredmckinnon

      Title

      Can Anyone Help me Think Through this Setup?

      Post


           Hey folks,

           I hope someone can help me and maybe give a step-by-step of how to set this up.  It's probably far easier than I think.

           I'm trying to build a marketplace category mapper for several online marketplaces.   Each marketplace has it's own category taxonomy that is required for products.

           Here's a synopsis of what they have and need:
           Amazon.Com:

           Requires a master category (only a few), a product type, and item type.

           NewEgg, Sears, and Buy.Com each require a category ID.   Each respective marketplace has huge spreadsheet of their categoryIDs.

           My goal is to take the Amazon item types (which are most defined and specific at the product level) and when selecting an Amazon Item Type --- it automatically populates the NewEgg, Sears, and Buy.Com categoryID fields based on a list.

           I got started, and currently have a FM database with 2 relationships:
           1:  Master Lookup
           Includes Amazon Product Category and Amazon Product Type records.
            

           2:  Product Categories:
           I have this setup where I can import product records ... and then, based on lookups, when I select the big Amazon Product Category, the Amazon Product Types that are only available for that category appear in the Product Type.

           I also had it setup where when you select the product type (again, whose values are dependent on which category was chosen), it then displays only the appropriate Amazon Item Types.

           This is where I'm stuck.
            

           Once I choose the Amazon Item Type --- I want the NewEgg, Sears, and Buy category IDs to automatically populate.

            

           So, for examle:

           Product category:  Consumer Electronics

           Product Type:  DVDPlayerOrRecorder

           Item Type:  In-Dash DVD Player

           When I choose "In-Dash DVD Player" --- I want it to populate NewEgg Category field with the correct ID from their taxonomy for that product, etc.

           I realize this means that somewhere, I have to do the work to load EVERY possible amazon item-type, and map each item-type to newegg, sears, and buy.com 1x.   The idea is to not have to look those values up every single time.

           I dunno if that makes ANY SENSE whatsoever!

        • 1. Re: Can Anyone Help me Think Through this Setup?
          philmodjunk

               Is there a one for one match between amazon itemtype, New Egg Category ID, Sears Category ID and Buy.Com category IDs? What you are describinng requires that . If a given Amazon Itemstype matches to two or more New Egg CategoryID's for example, then this won't work without the user selecting from this subset of matching New Egg Category IDs.

               Assuming that you have that one for one matching for 100% of your data, you can simply import all of this data into your master look up with one field for the Amazon item type and one field for each of the other category ID's

               It's also possible to set up Master Look Up with one Field for the Item Type, one for a BusinessID and one for the category ID, This then results in one record for each of the other businesses. In some ways this is more flexible as you can just keep adding records if you find you need to map to more retailers, but then the relationships for your look ups become more complex.

          • 2. Re: Can Anyone Help me Think Through this Setup?
            fredmckinnon

                 Phil,

                 Yeah, it's 1-to-1.

                 Can you walk me through setting it up?   Let me ask a few questions:

                 

                      Assuming that you have that one for one matching for 100% of your data, you can simply import all of this data into your master look up with one field for the Amazon item type and one field for each of the other category ID's

                 So, do you mean setup a database with 4 fields:

            a. amazon item type
                 b. newegg category ID
                 c. sears category ID
                 d. buy category ID

                 So, example - first record in this database:
                 a.  headphones
                 b.  70
                 c.  499999
                 d.  4500

                 Assuming that's right ....

                 Then, I have a new database where I actually import my supplier's datafeed ... so let's say I have these fields:
                 a.  supplierSKU
                 b.  supplierTitle
                 c.  supplierscategory
            d.  amazon item type
                 e. newegg category ID
                 f. sears category ID
                 g. buy category ID
                  

                 I import 10,000 rows of products  ... 

                 Let's say the first row is called "Audio Headphones".

                 I click over and choose "headphones" as the amazon item type --- and in doing so ... the newegg, sears, and buy categoryID fields AUTOMATICALLY, INSTANTLY populate with their respective values.

                 Is that how it works?  If so --- I'm guessing I have two tables here, right?  But I get lost trying to figure out how to relate them, and how to make these lookups happen

                  

                  

            • 3. Re: Can Anyone Help me Think Through this Setup?
              philmodjunk

                   The relationship should be:

                   AmazonTypes::ItemType = Items::ItemType

                   The two tables would be linked just by the amazon item type field. And you would not actually need the fields e. f. and g. as you can just add the fields b. c. and d. from the first table to the layout based on the second. That's probably the simplest setup, but as I've thought about this, I'm not totally thrilled with the lack of flexibility. If you decide you need to add yet another business to your list, you have to define additional fields to add them into your tables and then add them to your layouts. That may not be  big issue for you but there is another, a bit more complex way to organize the data via a third table and then adding another business takes much less (if any) design work.

                   Retailers-----<AmazonTypes------<Items(ImportedTable)>--------Retailers 2

                   You have one record for each pairing of Amazon ItemType and the categoryID of One Retailer. A Portal to AmazonTypes (and portals can be horizontal) would list each of the corresponding retailers and their Category IDs.