Difference between revisions of "Spreadsheet to sql asi sale"

Revision as of 02:47, 21 July 2018

2018-07-20 Friday

Notes on terms of this article:

• spreadsheet columns are analogous to relational database fields,
• product or item attributes map to (get stored in) database fields,

ASI plant sale 2018 has plants listed in a spreadsheet with the following fields (columns):

  price, name, description, image_filename, quantity

These item attributes map to Opencart table columns (fields) in the table oc_product and oc_product_description:

         price --> oc_product.price
          name --> oc_product_description.name
   description --> oc_product_description.description
image_filename --> oc_product.image
      quantity --> oc_product.quantity

So the info we've been given maps to two OC tables. There are two more tables we need update resepctively, to make given plant items appear in a given store and to make items searchable by one or more product (item) categories. These tables are oc_product_to_store and oc_product_to_category. These tables can be updated with the insertion of records which refer to the product ids of the items newly added to OC's table oc_products.

^ OC Category-Related Tables

Here are two key tables needed to hold categories which OC users define:

oc_test_cart> describe oc_category;

+---------------+--------------+------+-----+---------+----------------+
| Field         | Type         | Null | Key | Default | Extra          |
+---------------+--------------+------+-----+---------+----------------+
| category_id   | int(11)      | NO   | PRI | NULL    | auto_increment |
| image         | varchar(255) | YES  |     | NULL    |                |
| parent_id     | int(11)      | NO   | MUL | 0       |                |
| top           | tinyint(1)   | NO   |     | NULL    |                |
| column        | int(3)       | NO   |     | NULL    |                |
| sort_order    | int(3)       | NO   |     | 0       |                |
| status        | tinyint(1)   | NO   |     | NULL    |                |
| date_added    | datetime     | NO   |     | NULL    |                |
| date_modified | datetime     | NO   |     | NULL    |                |
+---------------+--------------+------+-----+---------+----------------+
9 rows in set (0.01 sec)

oc_test_cart> describe oc_category_description;
+------------------+--------------+------+-----+---------+-------+
| Field            | Type         | Null | Key | Default | Extra |
+------------------+--------------+------+-----+---------+-------+
| category_id      | int(11)      | NO   | PRI | NULL    |       |
| language_id      | int(11)      | NO   | PRI | NULL    |       |
| name             | varchar(255) | NO   | MUL | NULL    |       |
| description      | text         | NO   |     | NULL    |       |
| meta_title       | varchar(255) | NO   |     | NULL    |       |
| meta_description | varchar(255) | NO   |     | NULL    |       |
| meta_keyword     | varchar(255) | NO   |     | NULL    |       |
+------------------+--------------+------+-----+---------+-------+
7 rows in set (0.00 sec)

And a third table which holds data to associate OC categories with OC stores:

oc_test_cart> describe oc_category_to_store;
+-------------+---------+------+-----+---------+-------+
| Field       | Type    | Null | Key | Default | Extra |
+-------------+---------+------+-----+---------+-------+
| category_id | int(11) | NO   | PRI | NULL    |       |
| store_id    | int(11) | NO   | PRI | NULL    |       |
+-------------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)