Buying books was a way anyone could acquire a work of art for very little.
Solomon “Sol” LeWitt,
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
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 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.
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_unitat the moment when the sale was created. We have to save this value separately because the value for
producttable can change over time.
price– the product of
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
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.
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
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.
continuous_service table has almost the same structure as the
product table. The only differences are:
- We don’t have a
limitedattribute for obvious reason
default_automatic_prolongation_periodattribute contains information about the period (e.g., 6 months) that will be added to
end_timeafter the service
continuous_service_sale_item table connects
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_roletable. 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
automatic_prolongation_period– this is the amount of time (e.g, 6 months) added to
- It has the same value as
continuous_servicetable at the time when the record was created. We can change its value in the
continuous_servicetable, but we may still need to have the original data stored in 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.
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.
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
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:
default_automatic_prolongation_period are left off the table.
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.