Creating Tables for Products and Services

Selling products and services can be two very different propositions. This originates in their differing definitions and real-world representations. Previously in this series, we discussed the table basics in the context of database design and sales. In this post, we’ll analyze the differences between products and services, how they impact the database model, and how we can accommodate both on one database.

What Is a Product?

When we think of a product, we usually think of something we can touch: a book, a bicycle, or a computer. Similarly, from the buyers’ perspective a product is something tangible. Of course, there are products that do not have a true physical representation, such as computer operating systems or software suites. Tangible or not, all products share at least some similar characteristics:

• Usually are physical (book or bike) or have a measureable presence (computer software)
• Must be stored in a warehouse and ordered from a manufacturer or seller
• Require supply chain management
• Are produced in advance and offered completed
• Priced before purchase, and usually only paid for once

What Is a Service?

Services don’t usually have physical representation. Instead, they are often performed on behalf of a customer: building a database, answering incoming calls, or managing a social media account are all services.

Some services are one-time services, such as a dental appointment. This type of transaction is normally billed by the number of visits (each dental appointment costs €100) or by type of service (a facial massage costs €20 but a back massage costs €40).

Other services are continuous services. They may be supplied on demand (Internet access) or always be on (electricity, water, etc). These often have either a fixed price per month (flat rate Internet access) or per unit used (kWh for electricity). Continuous services may require a contract that specifies how long the service will be delivered – an indefinite period, a set number of months or weeks, or a limited time that renews automatically.

What Do Services and Products Have in Common?

In a word, limitations.

When selling products, we’re often at least temporarily limited to the number of items we have in stock. Services share a variant of this; we’re limited by how much we can deliver to customers. This usually comes down to limited working hours, space requirements, and personnel availability. For services that are provided 24 hours a day, our limitations tend to be more technical – the power supply chain is not working, there is not enough bandwidth available to meet all incoming requests, etc.

Ideally a database model should enable us to sell both products and services on one contract. To allow for the differences between products and services, we’ll use separate tables for each category.

Is it common to have different types of services and products in one system? Yes. Take your Internet service provider. When you sign up to initially get online, you get three variants of products and services:

• all physical equipment needed, such as modem and cables (products)
• the technician’s working hours to set everything up (one-time service)
• access to the Internet (continuous service with a 12-month contract)

Of course, most ISPs usually give us the physical equipment and the technician’s hours as part of our first month’s payment. Should we need additional equipment or another visit from the installation technician, though, we have to pay another one-time service charge or equipment charge.

Now that we’ve delineated what differences and commonalities products and services have, as well as their pricing variants, let’s dig into our database model. Parts of this model (the user_account, status, user_has_status, role, user_has_role, sale, sale_status, contract and client tables) were presented and described in previous articles. In this article, we’ll focus on two segments that are intended to store all relevant data for selling products and services.

The Product Segment: Stock, Product, and Product_sale_item Tables

As mentioned above, we need to store product data, connect sold products to specific sales, and contract and track stock status. The tables we need for this are the product table, the stock table, and the product_sale_item table.

The product table stores details about products we intend to offer to our clients. The attributes are:

• name – the name of product within the system
• price_per_unit – the cost of product per unit
• basic_unit – the base unit of product sales (e.g., piece, kg, liter)
• tax_percentage – the current tax percentage for that product. We must assume that tax percentage isn’t the same for all products.
• limited – a Boolean, with its value set to True if our stock is limited and False otherwise. (e.g., We can order from the distributer at will for our store or we can be sure we’ll have the product when needed)
• active_for_sale – if this Boolean attribute is False than we’re currently not offering that product for sale; if True, we can offer it to clients.

The product_sale_item table connects products with the relevant sale. Of course, we must assume that we’ll have multiple items related to sale, so we need to have a many-to-many relation.

This table’s attributes and their meanings are:

• quantity_sold – the quantity of product that was sold
• price_per_unit – this is the same value as product.price_per_unit at the moment when the sale was created. We have to save this value separately because the value for price_per_unit in the product table can change over time.
• price – the product of quantity_sold and price_per_unit. This small redundancy helps us avoid making the calculation within queries. Generally, the sum of all item prices belonging to the same sale should be equal to sale.sale_amount.
• tax_amount – the tax amount for the item(s) on the receipt.
• sale_id – the id of the sale transaction that this item belongs to.
• product_id – the product id related to this item.

The stock table is used to store data about the current quantity of products we have in stock. This table’s primary key is product_id, which is also the foreign key referenced as product.id in the product_sale_item table. This is obviously a one-to-one relation, since both tables have the same primary key. I used it here to show the proper usage of one-to-one relations (in my opinion). It can be wise to store stock information separately from the other data in the product table because stock is something that is a real-life entity, not just a piece of information. Also, when updating data in stock table we won’t lock data in the product table.

Attributes within the stock table are:

• product_id – the primary key of stock table, which also references the product table.
• in_stock – the quantity of a specific product we have in stock.
• last_update_time – the most recent time the product’s stock status was updated.

For the purpose of this article, we’ll assume that in_stock is updated properly with whatever warehouse software we’re using.

Splitting the Service Segment: Continuous and One-Time Sales

Now that we’ve set up the product tables, we’ll move on to services. As we already discussed, services can be divided into two groups: one-time and continuous delivery. Accordingly, we’ll split the service segment into two sub-segments, one for continuous services and the other for one-time services. Although the data is very similar in both cases, the difference is significant enough to make this extra step important. Enough to do it so.

As mentioned above, continuous services are available 24 hours a day prolongation after the initially agreed period has passed. They may or may not have contracts, and the contracts or service periods may or may not be automatically renewed.

The continuous_service table has almost the same structure as the product table. The only differences are:

• We don’t have a limited attribute for obvious reason
• The default_automatic_prolongation_period attribute contains information about the period (e.g., 6 months) that will be added to end_time after the service end_time expires.

The continuous_service_sale_item table connects continuous_service and sale in the same manner that the product_sale_item table connects product and sale tables. It contains all continuous services. The main difference is in the attributes, which include:

• salesperson_role_id – this relates to the user_has_role table. This relation tells us who sold that service
• start_time – the expected start time for the service
• end_time – the expected end time for the service
• automatic_prolongation – this is a flag that tells us if additional time will be added to end_time
• automatic_prolongation_period – this is the amount of time (e.g, 6 months) added to end_time after end_time expires
• It has the same value as default_automatic_prolongation_period in the continuous_service table at the time when the record was created. We can change its value in the continuous_service table, but we may still need to have the original data stored in the sontinuous_service_sale_item table.

The start_time attribute is mandatory since we must know when we started providing service. Conversely, end_time is not mandatory because we don’t always have a set ending time for all services. For example, if we signed a contract with an electric company provider, it might be for an indefinite period, and the company will only stop providing service at our request.

One-time services are usually appointments. These normally require time and place information reflecting where and when the service will be provided. into more detail of time scheduling. However, we’ll leave time scheduling out of this model for the present and focus on the table below.

The one_time_service table is a simplified variant of the continuous_service table. The only difference is in the price attribute. We can think of price in this setting as a basic service price, since we wouldn’t know the full price until after the service is completed. Going back to our dental appointment illustration, we wouldn’t know the cost of dental services until after they were finished. The final bill could be much more than expected, so after our toothache we could also have a headache.

The one_time_service_sale_item table connects the one_time_service table with the sale table in the same manner that the continuous_service_sale_item table connects the continuous_service and sale tables.

We can also look at the one_time_service_sale_item table, shown above, as a simplified version of the continuous_service_sale_item table. All shared attributes have the same meaning. Other attributes aren’t needed: quantity_sold, price_per_unit, start_time, end_time, automatic_prolongation_period, and default_automatic_prolongation_period are left off the table.

What’s Next?

There is a lot of room to improve this model. For example, we could:

• create packages of products/services that we discount when they’re purchased together
• set up a way to apply different tax rates based on region
• add better descriptions for products and services, based on editable dictionaries
• provide time and space scheduling for one-time services

These will be covered in upcoming articles. Don’t miss them! In the meantime, feel free to comment and ask questions about database modeling and any challenges you’re having.