[ic] Database Schema notes

[Josh Lavin]

Hello ic-users,

At the Ecommerce Innovation conference we had a rountable discussion
regarding the database schema for Interchange 6 / Nitesi.

You can see the original schema which we discussed here:
https://metacpan.org/module/HORNBURG/Nitesi-DBI-0.0092/lib/Nitesi/Database/Schema.pod

Following are my notes from the disussion. You can also find them as a
Github Gist here (if you want to fork, etc):
https://gist.github.com/perusionjosh/6984222

It is possible that I misquoted participants or got things wrong, so
feel free to provide corrections!

Best,
Josh

----

Database Schema Roundtable
Ecommerce Innovation, Hancock, NY
Oct 10, 2013
=================================

based on IC 5, many modifications, relational

products
--------

- weight from integer to decimal
- canonical_sku for variants to point to main product, because all
  variants are listed in this products table. Mark: true relational is
  to have a separate table to map the variants (class/instance example;
  emtpy canonical_sku records should be in a separate table).
- status - too general? what is the purpose? open to interpretation.
  Perhaps move to inventory table.
- inactive should be 'active', default '1'
- should add foreign keys
- Bill: uses separate pricing table for his stores. Could fallback to
  products:price, depending on your setup.
- have documentation on each table
- physical product vs. virtual product (perhaps a better way of thinking
  about products and variants; physical product is anything you can put
  in the cart)


product_attributes
------------------

- freeform, essentially replacement for adding additional fields to the
  products table.
- original_sku? akin to canonical_sku?
- have separate linking table, if we truncate this to "attributes" table
  and just have "code, name, and value". Linker table would allow you to
  specify what SKUs link to what attributes. Linker table could also
  specify sort.


inventory
---------

- Jure: FIFO column? Mark: BC example, inventory didn't have a quantity,
  but records, and you count the records to obtain the quantity -- each
  record could have its own location for the inventory item; you can
  lock a record (whereas you can't easily lock a value of a row);
  removes problem of concurrent inventory requests. Ron: this would be a
  great idea for serialized inventory, or tracking lots for recalls.
  Jure: good way to track supplier, if you source the same item from
  different suppliers.
- Mark: tracking table to follow an item across its life. May not be
  tracking each physical item to a row in the database, but as close as
  you can (perhaps a bin in the warehouse, but not indvidiual items).


navigation
----------

- some kind of taxonomy. Uses: categories, menus. 
- inactive --> active.
- count: how is this maintained? You could do a join if you wanted this.
- Mark: a database representation of the routes in Dancer
- scope: some kind of type. Needed?
- Mark: type is general (menu, category); scope is specific (which menu).
- template: click on "contact" menu, get "contact" template.
- Richard: isn't the view described in the route itself?
- alias: if you have multiple languages; multiple URLs but same products
- parent: the one above (t-shirts --> red t-shirts)
- Sam: image source for a menu item? 


media
-----

- entity for all images that could attach to navigation, product, etc
- Jure: script in background to fill in the data? 
- Mark: always a maintenance problem. Jure: check for orphans.
- Jure: everything that you hold in files could be here.


media_types
-----------

- scope: remove?


carts
-----

- Jure: track all cart changes? Mark: if so, track only in database,
  not session.
- created, last_modified: should be datetime (rather than timestamp); do
  it yourself.
- UTC time is the way to go. If your shop grows across timezones,
  you are OK.


cart_products
-------------

- add datetime
- position vs priority? position is reserved keyword? rename... 
- priority: not used. remove.
- Richard: could order by datetime, if using seconds.
  auto_increment? serial?
- Mark: have primary key (serial type), sort by that.


users
-----

- last_login: same as modified? no. should be datetime.
- Jure: auditing? Bill: perhaps general audit table, rather than
  user_audit. log to a file? import to db later? Ron: trigger-based
  auditing entries? admin could decide what tables to track.
 

permissions
-----------

- should permissions belong to roles. Usually, yes.
- uid: drop this column? Any user should acquire its permission
  from the roles
- too complex for roles to contain roles
 

addresses
---------

- Richard: not aid, but address_id; same for uid --> user_id.
- Mark: name of primary key. key is name of table + "_id". Easy to see
  what a foreign key refers to.
- Jure: need address2.
- Richard: state code should not be limited to 2 characters (US-specific).
- Dave: change zip to post code or postal code. But Jure says zip is
  generally recognized.
- Mark: you need to freeze the conditions associated with an order, so
  they can't change address record that is associated with a
  transaction. Same with orderline: you can't just reference products
  table. Basically, addresses is an insert-only world.


transactions
------------

- weight: Richard: is this total weight of all items in the cart? Yes,
  and change to decimal.
- change name to "orders"?


orderline
---------

- change name to "orderlines"?
- Richard: quantity_shipped field? partial shipping is going to require
  a linker table. To make it easy, remove this field.


payment_transactions
--------------------

- change name to "payments"?
- to record multiple payments on the same order.
- Mark: we have each transaction with the gateway recorded here:
  authorization, capture, void, etc.


settings
--------

- dynamic configuration for the client.
- site: for multi-site setup.
- similar to the variables table.
- site: purpose? expand length if putting domain names here?


-- 
Josh Lavin
Perusion -- Expert Interchange Consulting    http://www.perusion.com/
... ask me about job opportunities ...