Last active
December 25, 2015 13:29
-
-
Save jlav1n/6984222 to your computer and use it in GitHub Desktop.
Interchange 6 / Nitesi Database Schema roundtable discussion -
Ecommerce Innovation, Hancock, NY -
Oct 10, 2013
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
Database Schema Roundtable | |
========================== | |
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? |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment