The problem and the possible solutions
Most online stores have a common problem: they sell several families of products and each of them has different attributes. For example, shirts have a color and a size, while cell phones have a brand and a model. For this reason, a reasonable solution may be to store them in different tables. But they also have some important common attributes, like price or avaibility; and those attributes are read by many queries, so it would be comfortable to have those families in one table.
The worst solution is to create one table with a lots of columns which will be used only for few records. The historical solution is view-based. MariaDB introduces Virtual Columns, so there is now a Virtual Columns-based solution.
In this article we will discuss the view-based solution. Virtual Columns will be explained in a future article.
Ok, let’s CREATE what we need
Here’s the SQL code:
CREATE TABLE `shirt` ( `id` TINYINT UNSIGNED NOT NULL AUTO_INCREMENT, `price` DECIMAL(10, 2) NOT NULL, `avaibility` SMALLINT NOT NULL, `size` ENUM('xl','l','m','s','xs') NOT NULL, `color` ENUM('yellow','white','black') NOT NULL, PRIMARY KEY (`id`) ); CREATE TABLE `cell` ( `id` TINYINT UNSIGNED NOT NULL AUTO_INCREMENT, `price` DECIMAL(10, 2) NOT NULL, `avaibility` SMALLINT NOT NULL, `brand` CHAR(50) NOT NULL, `model` CHAR(50) NOT NULL, PRIMARY KEY (`id`) ); CREATE VIEW `product` AS ( SELECT `id` + 0 AS `id`, `id` AS `origin_id`, 'shirt' AS `family`, `price`, `avaibility` FROM `shirt` ) UNION ( SELECT `id` + 1000 AS `id`, `id` AS `origin_id`, 'cell' AS `family`, `price`, `avaibility` FROM `cell` );
I think that the tables don’t need to be explain. There is a table for every product family, and they could be 100 or 1000; but for this tutorial, 2 are enough.
The view contains the common attributes of the different product families (price, avaibility). To gather them in one view, we use a UNION statement. Plus, the view contains some columns that need some to be explained.
Points of VIEW
`family`: This is a constant value, but rows located in different tables have a different value for `family`. That’s exactly what we want, so that we can distinguish shirts from phones.
`id`: We want to have a unique id for all products, disregarding their family. But differenct tables may contain many identical `id` values, so we can’t rely on their `id`. That’s why we need to create an artificial value inside the view.
Note that in this example shirts and phones have a TINYINT Primary Key. This is reasonable, because a store can hardly sell more than 100 different types of shirts or cell phones, and the maximum value for TINYINT UNSIGNED is 255. We need to find a comfortable value that is higher than 255: 1000 is a good candidate.
Now, we decide that id’s of product of different families are in different ranges. So the shirts id’s are 1..255. But id’s of cell phones are 1000..1255, so the value is unique. We do this by adding 1000 to the cell phones id’s.
This value can be used for SELECTs which read common values for specific rows in the view, not in the basetables.
`origin_id`: We can’t use the `id` value to UPDATE or DELETE individual rows, because the VIEWs based on a UNION are not updatable. To execute those operations, we must run our statements directly on the tables. But to do this, we need to know their original `id`. That’s why we have an `origin_id` field: it matches to the `id` columns in the basetables.
The distinction between `id` and `origin_id` is not very common. I like it, because it allows me to SELECT a signgle row from the view, when/if I need it; however, this rarely happens. So you may want to include the basetables `id` in your view without any alias, and forget the artificial value.
It’s important to remember that the view is not insertable or updatable: you will SELECT from the view, but insert, update and delete from the basetables.