Relational database trouble in a custom e-commerce web application

Question

First of all, I apologize for the bad English you are about to read...

I'm trying to develop a little e-commerce web application (from scratch - without using platforms like Magento, OpenCart, Shopify...) for a pizza delivery in the city where I live. The restaurant also sells some italian food, like pasta, fish and meat.

I'm stuck in a relational database problem, I will explain what I did in the database. I will write the tables structures followed by some examples records.

Unlike the pastas, the pizza's price varies according to size (an attribute).

The data will be displayed in the following way (please see the picture below):

Showing a pizza example record in the front-end. When the user selects the size, the price will be displayed below and two controls to add or substract the quantity of that product (with that size) will also be displayed.

This is the case of one pizza with one attribute (an attribute that affects the price), because there are some attributes that not affects the price, i.e: the cooking or doneness. Another case is that a product that have more than one attribute that affect the price.

In summary:

1. Product without attributes, it has an only one price.
2. Product with only one attribute that affect the price.
3. Product with two or more attributes that affect the price.

MySQL Tables:

Categories(ID, name):
1, Pizzas
2, Pastas

_

Products(ID, category_id, name, description)
1, 1, Margherita, Lorem ipsum
2, 1, 4 Stagioni, Lorem ipsum
3, 1, Capricciosa, Lorem ipsum
4, 2, Bologna, Lorem ipsum
5, 2, Pesto, Lorem ipsum

_

Attributes (ID, name)
1, Size
2, Cooking

_

Meta_attributes(ID, attribute_id, name)
1, 1, Small
2, 1, Medium
3, 1, Big
4, 2, Blue
5, 2, Medium well
6, 2, Well done
7, 2, Overcooked

_

meta_attributes_values(ID, product_id, meta_attrib_id, value)
1, 1, 1, 12
2, 1, 2, 16
3, 1, 3, 19
4, 2, 1, 14
5, 2, 2, 18
6, 2, 3, 20

_

In this schema, a product can have a value if and only if it has a meta_atrib and in order to have an meta_atrib it must have an attrib. But the pasta is "linear" it no have any attrib, for one pasta product there are only one price.

Questions:

1. How should be the database to handle all these cases?
2. What about special cases where one attribute influences another? For example, suppose that the price of a pizza varies according to its size (this is true), but suppose that it also has an attribute called "extra" and that the price of the extra attribute varies according to the size of the pizza, Because being larger will require more. I know that the example is not very clear but I hope I have made the case clear and express myself well.

Thanks for reading!

Answer 1

It's important to note that the schema you've described doesn't represent an actual order, it represents the abstract concept of a pizza. A graph of Products, Attributes, Meta-Attributes, and Values is far more complicated than an itemized order needs to be.

What's really in an order? There are Products, each of which has a base price; and there are, as you note, things which affect the price of a Product. These modifiers come in at least two types:

• Additive modifiers tack on a flat sum to the base price. A "medium" pizza costs $4 above the base; a "large" costs $7 more.
• Dependent modifiers change the base price after it's adjusted by additive modifiers. The simplest form of dependent modifier is a multiplier: whatever the adjusted price of a pizza is, one with "extra hot peppers" costs 0.10x more than that.

With any luck, that's all you have to deal with. If "extra peppers" instead costs $0.50 on a small, $0.60 on a medium, and $1.00 on a large, you have to track all three and correlate with the size modifier since the addition isn't a consistent function of adjusted price. Treating additive modifiers like size independently -- for example, by having base, medium, and large prices in Product -- may be more effective in that case.

It would be possible to achieve a simpler representation still by treating products and attributes identically and storing them in a single table with a foreign key to itself to represent the parent-child relationship. Effectively, you'd have no Products, only Attributes. And "Margherita" would be an Attribute that adds $12 to an item base price of $0.

But getting back to the concrete, if you need to track Orders with Order_Items too, even a one-row-per-attribute solution is unwieldy since you have a profusion of foreign keys in each line item of the order. In this case, it may be best to store your sub-items (or everything, if you roll it all into one table) in a JSON field, such that your Order_Items table looks like this:

id order_id subtotal attributes
1  1        17.60       [{"name": "Margherita", "adds": 12.00}, {"name": "Medium", "adds": 4}, {"name": "Extra hot peppers", "multiplier": 0.10}]
2  1        12.00       [{"name": "Pesto", "adds": 12.00}]

This is a) denormalized and b) breaks referential integrity. Both of these, in this instance, are good things! If you ever adjust prices or even take something off the menu, you don't want to screw up your bookkeeping or trip a foreign key constraint error.