Strategies for Database Schema Redesign on Un-Normalized Group of Order Tables in Inventory System

by Brian Bruman   Last Updated July 12, 2019 04:06 AM - source

Being my first database-driven and SQL powered program, I'd created this more or less on-the-fly, learning as I go.. trying to be cautious- but more or less created with the intent of simply working..little foresight into longevity, stability, dependability, organization -- all the rest.

Case in point is an eCommerce store. Numerous Selling Channels, in some cases Numerous Accounts on said Channels.

What transpired out of that was a table for each Channel, and if the Channel had more than one account, a table for Each Account.

Current Table setup for Orders

╔══════════════╦═════════════════════╦═════════════════════╦═════════════════════╗
║ AmazonOrders ║ eBayOrders_account1 ║ eBayOrders_account2 ║ eBayOrders_account3 ║
╚══════════════╩═════════════════════╩═════════════════════╩═════════════════════╝

And actually up to 5 different tables for eBayOrders from different accounts.

Doesn't stop there.

Have tables in my database for..

╔═══════════════╦══════════════╦═══════════════╦═══════════════════╗
║ BonanzaOrders ║ NewEggOrders ║ ShopifyOrders ║ WooCommerceOrders ║
╚═══════════════╩══════════════╩═══════════════╩═══════════════════╝

And that's not even all of it to be honest, but that should give you a picture of how it's set-up.

To delve deeper, each table has roughly the same data or columns, or at least there is a "base" for the data I was looking to acquire and insert into the database.

Example:

CREATE TABLE `WooCommerceOrders` (
  `id` int(11) NOT NULL,
  `OrderID` int(11) DEFAULT NULL,
  `OrderLineItemID` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `BuyerEmail` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `Title` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `SKU` varchar(80) COLLATE utf8_unicode_ci DEFAULT NULL,
  `Quantity` int(11) NOT NULL,
  `TransactionPrice` decimal(6,2) DEFAULT NULL,
  `processed` tinyint(1) NOT NULL DEFAULT '0',
  `createdtime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

A good contrast would be an eBay Store Table, which, out of request, I added a few more columns to store data such as associated fees particular to eBay, so forth.

CREATE TABLE `eBayOrders_account1` (
  `id` int(11) NOT NULL,
  `OrderLineItemID` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `SalesRecordNumber` int(11) DEFAULT NULL,
  `BuyerUserID` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `BuyerEmail` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `Title` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `SKU` varchar(80) COLLATE utf8_unicode_ci DEFAULT NULL,
  `Quantity` int(11) NOT NULL,
  `TransactionPrice` decimal(6,2) DEFAULT NULL,
  `FinalValueFee` decimal(6,2) DEFAULT NULL,
  `PayPalFee` decimal(6,2) DEFAULT NULL,
  `processed` tinyint(1) NOT NULL DEFAULT '0',
  `createdtime` datetime DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Quick breakdown.. usually what was needed was two Order Lines to set up a Unique Key restraint within the table to avoid duplicates. Reason behind OrderID and OrderLineItemID which is kind of a sub-order of the original OrderID that WooCommerce calls.

eBay has a OrderLineItemID and SalesRecordNumber terminology they use to assign to orders, hence that.

The only outlier in this is my ShopifyOrders table, which has three Order Identifiers in the table. OrderID OrderLineID and OrderNumber.

Now I'm not trying to make this a novel, but I'm genuinely looking for advice on how to best undertake this database redesign.

I'm decently familiar with Relational Tables, Normalization, Foreign Key Relationships, etc now, least the general concepts and some practice.

My question is what would be an acceptable schema for such a database of orders and sources?

I shouldn't consolidate ALL of my order tables into one, should I? With a foreign key table of all the channels, linking them together. I think this might be okay if the number of columns were congruent through all the tables, -- but such as Shopify with three Order Identifiers, I think if I were to consolidate tables I should probably do so by Channel?

Meaning group my eBay Orders together into one, and assign another table to give it a unique key to relate back-- as all my eBay table and columns are the same.

Second thought, I should probably try to make the tables AS uniform as possible. This seems rather obvious now. Having one table indicate a Price column as different terminologies such as TransactionPrice, ItemPrice, so forth, only seems to serve to complicate.

Fees such as FinalValueFee, PayPalFee, for eBay Orders -- seems best served in a relational table and not the table itself.

Any other tips or strategies? I'm still learning as I go, and this is hopefully my final reformation of this to get it down.

My main curiosity and intent for post is.. how should I group/consolidate the tables? My experience in past normalization techniques would infer me to want to put all orders from all channels into one table, but (and I could be wrong), I don't think that would be best served here, for organizational and data-column reasons. Should even the eBay order tables be grouped?

Before I delve in and try and clean up some of my previous mess, how might this schema be designed to best serve the data in the system?

Huge thanks this has been an enormous feat for me to try and get right, any opinions or tips would be grateful.



Related Questions


Normalizing/combing mutliple tables with similar data

Updated October 24, 2018 14:06 PM

Database Table Design

Updated July 03, 2015 13:02 PM


Orders and Quotes Tables - Database Design

Updated May 11, 2017 13:06 PM