I need to import value from CSV to a table but don’t know how to design it. If I should save all value to one table or some value to another table. If it is better to save to different table, which value should I have in different table?

Also I don’t know how CSV structure should be, right now it look like this and imports to only one table.

Table:
product, articleid, manufactur, category, price, pimg, aurl

CSV from shop 1:
Apple iPod Nano 8GB; 4578;apple; MP3;1500; www. shop1.com/ipod.gif, www. shop1.com/articleurl

CSV from shop 2:
iPod Nano 8GB; 4848;apple; MP3;1000; www. shop2.com/ipod.gif, www. Shop2.com/articleurl

    I have a little tool to create a (very simple) CSV import PHP code and file uploader.

    Generator

    You can get ideas from it. You can use fgetcsv to render an array from your CSV data.
    fgetcsv()

      Hi, great tool. Not sure if it help for this problem but nice tool.

      My problem is wow i shuld design database and or product-table. I made a litle function to import CSV to a table but need help to design product-table. I’m not sure if saving all value (as i do now) to only one table is best thing to do or if I should split to different table? Example save product to one table, category to another table, manufacture to another?

      Any idea what is best to do in this case, save all value to only one table or is it better split to different table?

        Okay.
        lets determine the relation types in your products.

        -One item need to have more then one image urls ( pimg, aurl )?
        If yes, you need to use imageurls table, and the item's primary key should saved as a foreign key, and a type field should use to save if its a pimg or a aurl image.

        -One item would saved in more then one category?
        If yes, you need to use category and product_category_connector table to save product and category id pairs in multiple lines to realize many-to-many relationship.

        -If i were you i were save the manufactur into a separate table. When you make search engines, its a bit faster to select the records by their foreign keys only. If you're using a normalized DB model, you won't duplicate contents like:"category name", or manufacturs.

          Biggest deferens will be as below from CSV to CSV i think. As example this CSV file is from www. shop1.com

          CSV is like this
          Apple ipod Nano 8GB; 4578;apple; MP3;1500; www. shop1.com/ipod.gif, www. shop1.com/articleurl

          And table is one temp table with field: product, articleid, manufactur, category, price, pimg, aurl

          product: title of items, let’s say "Apple ipod Nano" and it can be deferent from CSV to CSV depends on how one shop spell it from another shop.

          pimg: link to product image from shop1.com, only one url to image but because same items also possible is on other CSV-file from other shops, there is possible there is link to same images but with only different link. I only want show/use one url with this item.

          aurl: link to article for this item on shop1.com

          articleid: not sure about this but it will be same product number from all shops? Example Apple may use code/id 5454545 for "Apple ipod Nano 80GB" which usually used by shops also?

          One item would saved in one category, in this case say MP3 but there is possible shops use or spell different in theirs CSV-file. Im not sure how to prevent duplicate for this and manufacture and sametime have it connected to this item and shop if i saving to different table. Import function just import everything to an temp-table as it is now but from here there maybe possible filter data to different table?

          Probably possible, but then how to connect/link "Apple" and "MP3" to this item and this shop I don’t know.

            If i were you, i were build a unique product table to store the product names.

            Lets say i have: "iPod Nano 8GB" it will generate an ID: 1

            if a shop is sending you "Apple iPod Nano 8GB" you the one who need to make a search engine by its words, search for "fulltext search" engine. On an admin panel you have to retrive the same records to be able to use that ID in your product_details table. Yes, a new table will hold extra information to save article url, prize for shop1, and extra informations like image about that product.
            New table for categories, a connector table to refer products into categories is suggested.

            plus:
            Your admin panel would have some features , like:
            - merge two duplicated product names (and replace the redundant ID's in the relational tables) together. To save the original item names in a temp field to be able to make exports for the shops.

              Write a Reply...