A Data Model for an Online Musical Equipment Shop

What kind of data model supports an online musical equipment store?

Musical equipment shops used to be one of those old-fashioned industries, one that you wouldn't expect could (or should) adapt to the online world. It wasn't unreasonable to paint such a picture. After all, you can't choose your ideal guitar based solely on magazine pictures, can you? Musicians and wannabe-musicians need to try various instruments, hearing and feeling how each one responds when played.

However, manufacturers have been able to achieve more consistent quality over the years. Recording technology has become better, allowing musicians to record very detailed reviews of instruments. There are even multiple-instrument comparisons on YouTube and other sites, which means we can see and hear all the nuances of each particular instrument.

Now we can hear when an instrument sounds good just by going online. We can see it looks good. The price is right. We can rely on the manufacturer's quality control. If we can do all that from the comfort of our own homes, why not buy an instrument that way?

The Rise of the Online Musical Equipment Shop

Musical equipment shops recognized this new ability and introduced online shops. Such online shops (e.g. Thomann, Musician’s Friend) have online sales that equal traditional shops’ sales. In several years’ time, it’s projected that online channels will comprise two-thirds of total musical equipment sales. For customers, this means more comfort, more choice, and lower prices (because running an online shop costs less than running a traditional one).

When it comes to keeping track of products and sales, online and offline shops are not very different. However, there are major differences in contacting and communicating with customers, the customer service process, and the sales fulfillment process. The data model for an online music shop can be viewed as an imitation of an offline shop, with some additional areas.

So, considering the overall increase in musical equipment sales and especially the increase in online music shops, we can expect an increased demand for a data model to support such a shop.

Having said that, let's have a look at our online music shop data model!

The Data Model

This will be a simplified model. Covering everything would require a data model that’s too complex for an article. Therefore, this model is meant for a shop selling instruments only (e.g. guitars, drums, pianos, clarinets, etc), not accessories (e.g. strings, picks, drumsticks, spare parts, etc).

The data model consists of nine tables, which are divided into two subject areas. Those areas are:

• Items
• Customers and orders

Let’s discuss each subject area individually.

Subject Area 1: Items

This subject area contains information about the products sold in our online store. It consists of five tables:

• category
• country
• manufacturer
• instrument
• item

The first table is the category table. It stores a list of all the musical equipment categories that we sell. It consists of the following attributes:

• id – A unique ID for each instrument category.
• category_name – Defines each instrument category (guitar, keyboards, drums, wind instruments, etc). This is the alternate key of the table.

The country table is a simple list of countries. This table will allow us to store both manufacturers' and customers' countries. It has the following attributes:

• id – A unique ID for each country.
• country_name – The name of that country. This is the alternate key for this table.

This manufacturer table contains data about the manufacturers of the instruments we sell. It contains the following attributes:

• id – A unique ID for each instrument manufacturer.
• name – The name of that instrument manufacturer.
• country_id – References the country table and tells us the country where that manufacturer is located. The name – country_id pair is the alternate key of this table.

With only the above tables, neither we nor our customers will know what we are actually selling. We need to have more details about the equipment we offer. For that, we will use the instrument table. It will allow us to create a catalog of our products. This table contains:

• id – A unique ID for each instrument name.
• instrument_name – The instrument name or model number.
• manufacturer_id – References that instrument’s manufacturer.
• category_id – References that instrument’s category.
• description – A more detailed description of the instrument (e.g. “Fender AM Pro Strat Ash HSS MN SSB”).

The instrument_name – manufacturer_id pair is the alternate key of this table. We can expect that each manufacturer will use unique names for their instruments, but it’s possible that two different manufacturers could use the same instrument name.

After creating our catalog on a category and instrument level, we can move to the item level. We could be selling a vast number of instruments and categories; to keep track of our inventory, we have to create an item table. This table contains info on all the individual items that we have in stock. It has the following attributes:

• id – The unique ID of each item.
• instrument_id – References the instrument ID.
• serial_number – Our shop's internal SKU for that item (NOT the manufacturer's serial number). This is the alternate key for the table.
• description – A detailed description of that item.
• year_of_production – The year when the item was manufactured.
• country_id – References the country table and denotes the country of origin.
• price – The price of the item.

By designing the items subject area this way, we can avoid having additional tables for tracking inventory.

Now we can move to the second subject area of our data model, Customers and Orders.

Subject Area 2: Customers and Orders

This subject area is designed to store information about customers and their orders. It will help us keep our customers happy and keep the quality of our service high. There are four tables in it:

• customer
• customer_order
• order_item
• order_status

Let's see the purpose of each table.

For a customer to place an order, they have to create an account with our online shop. The customer table will store all this data. Thus, we need the following attributes:

• id – A unique ID for each customer.
• customer_name – The customer’s full name.
• email – The customer's e-mail address used to set up their account. This is an alternate key for this table.
• user_name – The username chosen by the customer during account creation. This is another alternate key for this table.
• password – The hash value of the password chosen by the customer.
• confirmation_code – A code sent to the customer during the registration process. It is used to confirm their account.
• confirmation_time – The time when the account was confirmed by the customer.
• country_id – References the country where the customer lives.

After their account is created, the customer should be able to place orders. To facilitate this, we need the customer_order table. This table will contain all customer order data and will allow us to create invoices and monitor payment and delivery status. The attributes are as follows:

• id – The unique ID of that order.
• customer_id – References the customer who placed the order.
• order_time – The time when the order was placed.
• delivery_address – The address where the customer wants their order delivered.
• preferred_delivery_time – When the customer wants their order delivered.
• order_status_id – The current status of the order.
• time_paid – The timestamp of when the customer paid for this order; this can be NULL because the customer may not pay at the same time the order is placed.
• time_canceled – The timestamp of when the customer canceled the order; this can be NULL because the customer may not ever cancel an order.
• time_completed – The timestamp of when the order is paid and packed for shipping but not yet sent. It will be NULL until we reach this step in the process.
• time_sent – The timestamp of when the order was shipped; it will contain a NULL value until we ship the order.
• time_delivered – The timestamp of when the order was delivered to the customer; it will also be NULL until the order is delivered.
• total_price – The total price of the items ordered.
• discount – The discount given to the customer (if any).
• final_price – The total price of the order after the discount is applied.
• active – Denotes whether an order is active or inactive. An order may become inactive if the customer manually cancels it or if the store automatically cancels it using predefined rules – e.g. we'll cancel any order that hasn't been paid within five days of being placed.

A list of all possible statuses is stored in the order_status table; a status is assigned at the same moment when the corresponding time value is set, so we can expect statuses like “order placed”, “paid”, “canceled”, “completed”, “sent”, etc.

If we take a look at our model right now, we can see there is a slight problem. It is obvious that the relationship between customer_order and item is many-to-many: Customers can place an order consisting of several items, or one item could be found on several orders, e.g. when an order is canceled by one customer and the same item was later ordered by another customer.

To solve this, we’ll need another table, order_item. It will contain data about the items being ordered and will serve as a connection between the customer_order and item tables. This table will have the following attributes:

• id – The unique ID of the item being ordered.
• customer_order_id – References the customer_order.
• item_id – References the ID of the item.
• price – The price of the item.

The customer_order_id – item_id pair is the alternate/UNIQUE key of this table.

In creating this table, we’ve taken the last step in creating our data model for an online musical equipment shop.

Got Some Ideas for Our Musical Equipment Store Data Model?

Every good data model is designed to suit custom requirements. In the context of a musical equipment shop, there are plenty of parameters that will require a more complicated data model than this one. However, I tried to give you a general overview of the requirements and a basis upon which you can build your own data model. Do you think something should be added to this model? What would make it more useful? Please let me know in the comments section.