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:
clienttable stores client data. The
last_namefields 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_IDattribute 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_idattribute is a reference to the
client_addressstores the client’s location. This information can be used as the default shipping and billing address in the
citytable is a simple dictionary containing a list of all cities included in our database.
countrytable 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.
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
unit– the basic unit used to measure a product, e.g. pieces, kilograms, pounds
price_per_unit– the price per basic unit
product_type dictionary is used to store different products categories. It contains only the
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.
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
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.
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
shipment_type_id– a reference to the
time_created– the actual time when the record was generated.
payment_type_ida reference to the
payment_typedictionary. 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
billing_addresswe will use information stored in
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.
discount– the customer’s delivery costs and any discounts we’ll offer them.
final_price– another piece of redundant data; it is calculated as
We’ll store all the products and services related to the shipment in the
shipment_details table. The
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
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
status_catalog_id fields are references to the
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
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_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?