Difference between revisions of "Spreadsheet to sql asi sale"

From Wiki at Neela Nurseries
Jump to: navigation, search
m
m
Line 27: Line 27:
 
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.
 
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.
  
 +
 +
== [[#top|^]] OC Category-Related Tables ==
  
 
Here are two key tables needed to hold categories which OC users define:
 
Here are two key tables needed to hold categories which OC users define:
Line 61: Line 63:
 
+------------------+--------------+------+-----+---------+-------+
 
+------------------+--------------+------+-----+---------+-------+
 
7 rows in set (0.00 sec)
 
7 rows in set (0.00 sec)
 +
</pre>
 +
 +
And a third table which holds data to associate OC categories with OC stores:
 +
 +
<pre>
 +
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)
 
</pre>
 
</pre>
  

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)