Going meta

Concept eCommerce Schema

Before a store owner can begin to sell products online, it is prudent that they have a product catalog that potential customers can look at on their browsers.

A product catalog can contain only a few items, in which case the standard WooCommerce point and click upload process is adequate.

However, once a vendor has over 100 items to sell on WooCommerce, the uploading and populating of the “product table” becomes a monumentally tedious and error prone.

More importantly, the hundreds of hours required to populate such a DB would be more wisely spent taking care of core business activities.

There are simple products, such the free WP CSV Import plugin I described in the previous post, that help mitigate the pain.

Unfortunately, these sorts of primitive upload plugins still require much work, and are less than ideal for handle large uploads. Moreover, product pictures still have to be uploaded manually (unless they are already available somewhere, in which case the store owner has to type or C&P hundreds if not thousands of links.)

Internal data relations in WooCommerce must be examined and corrected manually.

For example, it is possible for a CSV upload to identify a product quantity as 1, yet upon examination, a store owner will discover that the quantity descriptor for that product will be set to Out of stock.

Unfortunately, it does not appear that WooCommerce offers a solution to this problem.

Searching the Woothemes product catalog, one comes across a pricey Photography plugin, and that is pretty much it. This plugin costs $79, and appears to be limited in terms of the attributes that can be uploaded in batch mode to the WP database.

What is actually needed is a professional, robust, low-cost, and extremely user-friendly application that can quickly load large amounts Product data, including pictures, at the click of a button.

Since WP and thus WooCommerce make use of the MySQL db as their persistence engine, such an application would ideally – in batch mode – be able to accept XML or CSV files.

Providing it can acquire administrative privileges, it should be able first ALTER MySQL tables in order to drop any existing referential integrity constraints, populate the necessary WP and Woocommerce custom tables using a fast proc such as LOAD DATA INFILE, then reapply the Woocommerce schema’s RI, and of course provide the user with an error file for any products that failed to load.

In this manner, a store owner could be assured that the Woocommerce Product table(s), which no doubt many internal dependencies between fields (such as the one indicated above involving quantity and In stock), provides an accurate snapshot of their business.

Even for someone who is not a database designer or architect, this issue can be usefully visualized using the conceptual eCommerce schema shown above.  In this manner, an end user can begin to map his or her mental picture of the business to Woocommerce application, as well as being to identify any additional custom attributes that may be required (hello Metabox!)

I shall not get into this ER semantics of this toy schema here: suffice it to say that this is an abstract representation of a class transactional DB in 3NF. In future posts, I shall examine more closely how WooCommerce actuially uses the underlying WP tables and views to support ecommerce.

But for now, given that my client is impatient to load her catalog, I shall test a product that may be of most help to a harried store owner who wishes to spend as little time as possible clicking and typing endlessly in order to populate a product DB that may contain hundreds if not thousands of items and their associated descriptors.

This product is able to handle XML product files as well as zipped product images.

I’ll let you know how it turns out.