A Data Model for a Wine Store

Producing a great wine is a really complex process, one that takes many years to master. Offering and selling wines to customers is another complicated process. There are many stores specializing in only one product. If you want that product, you’ll go to that store. Wine stores are an example of what I’m talking about. What would be the data background of a wine store? Let’s find out.

In many ways, a wine store is like any other store. So, you can expect this data model to have most of the tables common to any other retail business. Still, there will be some details that will distinguish this model from others.

For the purpose of this article, let’s assume that we run multiple wine stores in distinct locations. We’ll need to order wine, enable registered customers to place orders, process these orders, and manage invoices.

The Data Model

The model consists of three subject areas:

• Bottles
• Stores & orders
• Customers & purchases

We’ll describe each of these subject areas in the order they’re listed.

Section 1: Bottles

In this subject area, we’ll cover the products we’re selling. While wine could be sold in units other than bottles, bottles are still the most common “package”. Thus the name of this subject area and its biggest table.

Two of the most important things about wine are 1) the location it’s made, and 2) who makes it. We’ll use three tables for this information.

First up is the country table. In this simple dictionary, we’ll store a list of UNIQUE country_names. These are countries where we have stores or where wine producers are located. Some of the most important wine-producing countries in Europe are Spain, France, and Italy.

Next, we need to define regions, i.e. parts of a country. When it comes to wine, regions can be more important than the country of origin. A few well-known wine regions are Savoie (France), Yarra Valley (Australia), and Maule Valley (Chile). For each region, we’ll store the UNIQUE region_name – country_id pair.

Then there is the producer table. In this table, we’ll store a list of all the wine producers whose wines we stock. For each producer, we’ll define a UNIQUE combination of producer_name – region_id as well as any additional details (if needed).

Wines are also categorized. Some of the most famous wine categories are Cabernet Sauvignon, Merlot, Pinot Noir, Chardonnay, and Riesling. We’ll store wine categories in the category dictionary. Each record here shall be UNIQUELY defined by its category_name.

We’re finally ready to describe the central table in this subject area: the bottle table. For each type of wine bottle we have, we’ll define:

• full_name – The wine’s full name.
• category_id – The category of that wine.
• label – The complete bottle label text.
• volume – How much wine the bottle holds.
• producer_id – References the producer of that wine.
• year_produced – The year this bottle was produced.
• picture – A link to the picture we’ll use to represent this bottle.
• alcohol_percentage – The percentage of alcohol in this wine.
• current_price – The current price.

The combination of full_name – producer_id – volume form the UNIQUE/alternate key of this table.

Section 2: Stores and Orders

The key prerequisite for smoothly running our business is to know how much of each product we have in stock. This information is the starting point for knowing how much to order and how much we can sell customers. To do this, we’ll need to define our stores, our inventory, and the orders we place with suppliers.

Let’s start with the store table. For each store, we’ll need the UNIQUE combination of the store_name and the city_id where the store is located, plus the store’s physical address, the store’s phone number, the store manager’s mobile number, the store’s email address, and all additional details.

Now we can define an inventory for each store. We’ll use the inventory table for that. Besides the alternate/UNIQUE key (which is composed of the foreign key pair store_id – bottle_id), we’ll record only the current quantity in our inventory.

Before moving on to orders, let’s define the other tables that are referenced in the order table.

First is the supplier table, which contains a list of our suppliers. For each one, we’ll store the UNIQUE supplier_name, the address, all available contact details (phone, mobile, email) and additional details in textual format.

The other table is the employee table. It contains records for all the employees that can log in to the application and take certain actions, e.g. placing orders. Please note that we won’t cover employee roles and permissions in this article. This would make the model too big and detract from the main topic. For each employee, we’ll store:

• first_name and last_name – The first and last name of the employee.
• username and password – The employee’s login credentials for our application. We’ll store the hash value of the password.
• phone and email – The employee’s contact details.
• insert_ts – When this record was inserted into the database.
• is_active – A flag denoting if this employee is still active within our company.

Orders are made with the intention of having enough bottles in stock to meet customer demands. For each order, we’ll store:

• order_number – The UNIQUE identifier we’ll use internally for this order.
• expected_delivery_date –The date we expect this order to be filled.
• time_placed – The exact time when this order was placed.
• time_canceled – The exact time when this order was canceled (in case that happened).
• time_delivered – The exact time when this order was delivered (in case that happened).
• supplier_id – The ID of the relevant supplier.
• store_id – The store where the order should be delivered.
• employee_id – The employee who placed this order.
• order_price – The price we’ll pay for this order. This value is NULL until the supplier updates it.

Please note that using “order” as a table name may not be the wisest choice. “Order” is an SQL keyword, so we have to escape this table name when using it in queries.

The last thing we need to do in this subject area is to define all the items which are part of an order. To do that, we’ll use the order_item table. For each UNIQUE order_id – bottle_id pair, we’ll define the desired quantity and the supplier will update the order_price.

Section 3: Customers and Purchases

The last subject area is the one where we’ll manage our actual sales data. To do that, we’ll need to define customers and record what we actually sold them. We can expect that we’ll have registered customers as well as walk-ins that just come into our shop and buy a bottle of wine. We’ve covered both options in this model.

The idea is to allow the customer to register with our application, which gives them the ability to reserve the bottles they want. A list of all our registered customers is stored in the customer table. The table is similar in structure to the employee table. For each customer, we’ll store:

• username and password – The customer’s credentials for accessing our application.
• customer_name – The customer’s name.
• address – The customer’s address.
• phone and email – The customer’s contact details.
• details – All additional customer details, in unstructured textual format.
• confirmation_code and confirmation_time – The code we’ve sent to the customer that completes the registration process and the moment when this process is completed.
• insert_ts – When this record was inserted into the database.

The customer can place orders with us much like we place orders with suppliers. The customer orders are stored in the customer_order table. For each order, we’ll have:

• order_number – A UNIQUE identifier we’ll use internally for this order.
• customer_id – The ID of the customer who placed this order.
• store_id – The store where the order will either be picked up by the customer or delivered to them.
• expected_delivery_date – The date when this order should be fulfilled.
• time_placed – The exact time this order was placed.
• time_canceled – The exact time this order was canceled (in case that happened).
• time_delivered – The exact time this order was delivered (in case that happened).
• order_price – The total price at the moment this order was placed.

A list of order items is stored in the customer_order_items table. For each order, we’ll store the ID of the related bottle, the quantity ordered, and the price (per single item) at the moment the order was placed.

The last two tables in our model are related to charging customers and collecting payments.

The first is the invoice table. Each invoice contains the following details:

• invoice_number – A UNIQUE internal identifier that we’ll use for invoices.
• store_id – The store where this invoice was issued.
• customer_order_id - The ID of the relevant customer order (if we had one).
• customer_id – The ID of the customer who placed this order (if it was a registered customer).
• employee_id – The employee who created this invoice.
• invoice_total – The total amount the customer should pay for this order.
• time_created – The exact time this invoice was created.
• time_due – The exact time this invoice became due.
• time_paid – The exact time this invoice was paid.
• insert_ts – When this record was inserted into the database.

The last table in our model is the invoice_item table. This is where we’ll store all items appeared on our customer order invoices. We’ll need the following details:

• invoice_id and bottle_id – The IDs of the relevant invoice and bottle. This pair also forms this table’s alternate/UNIQUE key.
• quantity – The quantity of the item on the referenced invoice.
• item_price – The price per single item when the invoice was generated.

What Do You Think About Our Wine Store Data Model?

The model we discussed today should contain everything needed to run a wine store retail chain. Do you think we lack something? Or perhaps we went into too many details? Please tell us in the comments below.