0

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 reasons of organization and data-column inconsistency reasons. Should even the eBay order tables be grouped? Or am I missing something simple and should break it down in some other way?

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.

1
  • An "order" is composed of multiple "items", yes? Well, don't mix the two things in a single table.

  • Similarly eBayOrders_account1 seems to be a mashup of orders, orderitems, users and payment.

  • In general, do not have multiple 'identical' tables. I am guessing that is what is happening for eBayOrders_account*

  • TransactionPrice is limited to only 9999.99, yet Quantity can be a whopping 2 billion.

  • A price in the Transactions table could be simply named price. Ditto for the Items table, except that you might want both a UnitPrice and TotalPrice, especially if there are quantity discounts.

  • I see no indexes. Are they added later?

  • Are there any significant differences between "Amazon" orders and "EBay" orders? It does not hurt to have a couple of NULLable columns in a single Orders table to allow for minor differences.

Munch on those suggestions, then start over in a new Question with a fresh set of CREATE TABLEs, including the indexes. This is just round 1 of several.

  • Interesting opinion/insight thank you. I'm trying to visualize what the table would look like adhereing to everything you suggested. eBay and Amazon are fairly similiar, yes, with different terminology. Keys I didn't include you're right, but they are something like this... ALTER TABLE AmazonOrders` ADD PRIMARY KEY (id), ADD UNIQUE KEY unique_orders_index (OrderID,OrderItemId);` -- an Order can have duplicate OrderIDs but yes always with unique OrderItemIds within it. Going along with your first point, you're suggesting to split these two up? – Brian Bruman Jul 12 at 11:01
  • I wonder if you have a link to share that provides a visual example of how that would be done, or could quickly do a really simple mockup of how it might be done? That'd be a huge help, as that's the hardest thing to grasp. Everything else you're right on and I can make those corrections right away. Yes it is always tough going back and noticing/trying to fix all the mess you've made when you didn't know better-- it's definitely going to be a process :) Thank you very much I'm going to start scraping away at this today will keep all these in mind! – Brian Bruman Jul 12 at 11:06
  • @BrianBruman - 19,600 pages on "orderitems" in this forum. Most have split the tables in a somewhat valid way. Read several of them; some will be hard to grasp; some will may things much clearler. – Rick James Jul 12 at 13:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.