A Simple Online Store Model: Sell, Ship, and Get Paid

A lot has changed over the last 30 years, especially in IT-related domains, but few things have changed as much as how we shop. We still mostly buy milk and groceries at physical stores, but widespread online stores have led most of us to try some form of e-shopping. E-commerce stores have popularized many existing products and services, and they’ve introduced many more. Buying non-physical goods like subscriptions, software, music and movies in digital format is now very common. And yet, none of this could be possible without the development of various online payment methods. Today, we’ll take a look at a simple web store model, focusing especially on shipments, and we’ll discuss how to handle payments from a database design perspective.

A Quick Introduction to the Model

Our model is organized into 4 sections:

• Client – relates strictly to client data
• Product – contains all products and services offered to clients
• Shipment – deals with specific shipments and their details
• Payment – stores payment methods and related details

The main parts of the model are the “Shipment” and the “Payment” sections; however, we’ll discuss the “Client” and “Product” sections first.

Clients and Locations

The “Client” section is simplified and stores only very basic data. It contains three tables:

• The client table stores client data. The first_name and last_name fields are mandatory. For in iduals, these attributes will hold their first and last names; for businesses, they could be used to store the first and last name of the manager (or whoever our contact is). Private individual customers don’t have a company_name, so this field is optional. The VAT_ID attribute contains the client’s VAT identification number and can be treated as an external client ID. It’s not unique because occasionally two clients may share a VAT number. For example, in some countries craftsman as private person and his related business entity have the same VAT ID. This attribute is optional – in many cases we won’t know or even need VAT. The city_id attribute is a reference to the city table, while client_address stores the client’s location. This information can be used as the default shipping and billing address in the shipment table.
• The city table is a simple dictionary containing a list of all cities included in our database.
• The country table is a simple dictionary containing a list of all countries we serve.

Products and Services

The “Product” section is also simplified and contains only the most relevant data about our products and services. There are lot of possible improvements in this section, and the user should adjust it for their business specificities.

The product table is the central table in this section. It is used to store all the products and services that we’ll offer to our clients. The attributes in this table are:

• product_name – a product’s name
• product_description – a detailed product description
• product_type_id – a reference to the product_type dictionary
• unit – the basic unit used to measure a product, e.g. pieces, kilograms, pounds
• price_per_unit – the price per basic unit

The product_type dictionary is used to store different products categories. It contains only the type_name attribute.

We’ll use the stock table to store details about currently-available product quantities. The product_id is the primary key and a reference to the product table. We could have just added the in_stock and the last_update_time attributes to the product table, but I’ll keep it separated here. This way, the product table won’t need to be updated with every new purchase and every delivery to the stock. Only the in_stock and the last_update_time attributes will need updating.

Shipments

The “Shipment” section contains five tables that store details about client orders, order types, client addresses, and shipping costs.

The simplest table in this section is the shipment_type dictionary. We’ll use it to store values like “deliver product after payment”, “charge after delivery” and “send 2 days after order is placed”. These will determine when we’ll take delivery action related to the order date or payment date (which are stored in the shipment_status table).

Each shipment goes through several steps, from order to delivery. The status_catalog stores all possible values that we could encounter during this process: “order placed”, “payment confirmed”, “shipment sent”, and “shipment delivered”. The last status assigned to a shipment determines what actions are needed to complete the process.

The shipment table may be the most important table in our model. It is used to keep details about placed orders and to update data according to where the order is in the shipment process. The attributes in this table are:

• client_id – a reference to the client table.
• shipment_type_id – a reference to the shipment_type table.
• time_created – the actual time when the record was generated.
• payment_type_id a reference to the payment_type dictionary. We can assume that the payment type is selected when the order is placed.
• shipping_address – the address where we’ll ship our products. It’s mandatory data. In cases where we sell services online, we won’t have a real shipping address. Still, we could use this attribute to store an email address, account name, etc.
• billing_address – the address used on an invoice. It’s required because the billing address may be different from the shipping address. (For example, a large company has to put their headquarters’ address on an invoice, but the actual items might be shipped to a local office). By default, for both the shipping_address and the billing_address we will use information stored in client.client_address.
• products_price – the sum of all products prices in a given shipment. It is redundant data, but we’ll store it here to simplify future queries.
• delivery_cost and discount – the customer’s delivery costs and any discounts we’ll offer them.
• final_price – another piece of redundant data; it is calculated as
  products_price + delivery_cost – discount

We’ll store all the products and services related to the shipment in the shipment_details table. The shipment_id and product_id attributes are references to the shipment and the product tables. The quantity products ordered and the price per unit at the time of order are stored in the quantity and the price_per_unit attributes. The price attribute is one more redundant bit of information. It can be calculated as quantity * price_per_unit.

The shipment_status table connects shipments with statuses. We’ll use this table to store status history; it will relate values from the status_catalog table with each shipment and the moment when that action happened. Possible statuses include “order placed”, “payment confirmed”, “shipment sent”, and “shipment delivered”. The status with the most recent date is the current status.

In this table, the shipment_id and status_catalog_id fields are references to the shipment and status_catalog tables. For each status, we’ll store the date when shipment had that status (the status_time attribute) and additional notes if needed (the notes attribute). A shipment could have the same status assigned multiple times, so the shipment_id – status_catalog_id pair is not set as a unique value.

Payment Type and Details

The “Payment” section is composed of three tables. It’s intended to store all the relevant data about payments that relate to shipments.

We’ll define all the payment types in the payment_type dictionary. Remember that each shipment has a payment_type_id that denotes which type was used. Values for this dictionary could include “cash”, “debit card”, “credit card”, “PayPal”, and “wire transfer”.

Each payment type has its own set parameters. Due to the parameters’ diversity, we used the key–value structure instead of implementing additional tables specific to each payment type. The payment_data table holds these values. The payment_type_id attribute stores the value of the referenced payment type, while data_name and data_type refer to the screen name and type of the attribute we want to store. Some of the possible values that could be stored in this table are “amount”, “fees”, and “payment date”.

The last table in this section is the payment_details table. Obviously, shipment_id is a reference to the related shipment. The payment_data_id attribute stores the key definition, while the value attribute stores its actual value.

In this article, we’ve emphasized shipment details and payment methods. In most cases, shipments have almost the same structure. On the other hand, we can’t be sure which payment methods will be used. So we went with a key-value paradigm to support all possible situations.

There is a lot of room to improve this design, but it gives a good overview of what we could expect ‘under the hood’ of an online store’s web app. How would you improve this model?