How to model a database for prices that vary depending on their categories and parameters?

Question

My first post on DBA! Apologies in advance if I made any mistake.

Before jumping into the schema and tables, I would like to share what I am trying to achieve first. I am working on sort of a courier application, where I have some categories and each category has a pre-defined price.

But determining the price is a bit ugly (absence of symmetry and patterns; at least, I can't seem to find any) . I will give you an example:

Consider the following categories: Document, Heavy Document, Laptop, Carton, Heavy Carton.

1. Document: It's for the lighter documents, which are below 0.5kg. The price is 20$, fixed.

[price stored in the prices table: 20.00]

e.g. For an item of 300g, the price will be 20$.

2. Heavy Document: This is for the documents that are over 0.5kg. Unlike the Document category, it doesn't have a fixed price! Rather, it has a unit price: 10$ per kg, which will be applied to each kg exceeding 0.5kg.

[price stored in the prices table: 10.00]

e.g. For an item of 2kg, the price will be 35$ (1.5g = 15$ + 0.5 = 20$)

3. Laptop: Straightforward, 100$. Nothing special about it, no constraint whatsoever.

[price stored in the prices table: 100.00]

e.g. For an item of 2kg, the price will be 35$ (1.5g = 15$ + 0.5 = 20$)

4. Carton: Here comes an interesting one. Until now, there was only one dependency: weight. But this one has an additional dependency: dimension. This is somewhat similar to the Document category. For the cartons that are below 3 Cubic Feet(CF), the price is 80$ per CF. The difference between Document and Carton category is that the Document has a fixed price, whereas Carton has a Unit Price. But wait, there's more. There is an additional constraint: dimension-weight ratio. In this case, it is 7kg per CF. And if the item's weight crosses the ratio, for each extra kg 5$ will be charged. It's so confusing, I know. An example might help:

[price stored in the prices table: 80.00]

e.g. For a carton of 80kg and 2CF; the price will be 490$. Here is how:

First calculate the regular charge: 80$*2CF = 160$ Now let's figure out if it crosses Ratio: Since, 1 CF = 7kg, hence, 2CF = 14kg. But the item's weight is 80kg, so it crosses the ratio (14kg)

Since it crosses the ratio, for all the extra kgs (80-14 = 66kg), each kg will cost 5$: 66*5 = 330$. After adding it with regular charge: 330$+160$ = 490$.

5. Heavy Carton: This one is for the cartons having the dimension bigger than 3CF. The difference with Carton is the unit price. Heavy Carton is 60$ per CF.

[price stored in the prices table: 60.00]

e.g. For a carton of 80kg and 5CF; the price will be 525$. Here is how:

First calculate the regular charge: 60$*5CF = 300$ Now let's figure out if it crosses Ratio: Since, 1 CF = 7kg, hence, 5CF = 35kg. But the item's weight is 80kg, so it crosses the ratio (35kg)

Since it crosses the ratio, for all the extra kgs (80-35 = 45kg), each kg will cost 5$: 45*5 = 225$. After adding it with regular charge: 300$+225$ = 325$.

If you've read this far, I think I have convinced you that the business structure is really complicated. Now let's take a look at my categories schema:

+-------------------------+---------------------------------+------+-----+---------+----------------+
| Field                   | Type                            | Null | Key | Default | Extra          |
+-------------------------+---------------------------------+------+-----+---------+----------------+
| id                      | int(10) unsigned                | NO   | PRI | NULL    | auto_increment |
| name                    | varchar(191)                    | NO   |     | NULL    |                |
| created_at              | timestamp                       | YES  |     | NULL    |                |
| updated_at              | timestamp                       | YES  |     | NULL    |                |
| dim_dependency          | tinyint(1)                      | NO   |     | NULL    |                |
| weight_dependency       | tinyint(1)                      | NO   |     | NULL    |                |
| distance_dependency     | tinyint(1)                      | NO   |     | NULL    |                |
| dim_weight_ratio        | varchar(191)                    | YES  |     | NULL    |                |
| constraint_value        | decimal(8,2)                    | YES  |     | NULL    |                |
| constraint_on           | enum('weight','dim')            | YES  |     | NULL    |                |
| size                    | enum('short','regular','large') | YES  |     | regular |                |
| over_ratio_price_per_kg | decimal(8,2)                    | YES  |     | NULL    |                |
| deleted_at              | timestamp                       | YES  |     | NULL    |                |
+-------------------------+---------------------------------+------+-----+---------+----------------+

Also the schema of prices table (it's a polymorphic table, hoping to create a subcategories table someday):

+----------------+---------------------+------+-----+---------+----------------+
| Field          | Type                | Null | Key | Default | Extra          |
+----------------+---------------------+------+-----+---------+----------------+
| id             | int(10) unsigned    | NO   | PRI | NULL    | auto_increment |
| amount         | decimal(8,2)        | NO   |     | NULL    |                |
| created_at     | timestamp           | YES  |     | NULL    |                |
| updated_at     | timestamp           | YES  |     | NULL    |                |
| priceable_type | varchar(191)        | NO   | MUL | NULL    |                |
| priceable_id   | bigint(20) unsigned | NO   |     | NULL    |                |
| deleted_at     | timestamp           | YES  |     | NULL    |                |
+----------------+---------------------+------+-----+---------+----------------+

How can I improve this structure to keep things as dynamic and coherent as possible?

Answer 1

I realize this can be daunting. but the greatest challenge for schema design is to understand that the world cannot be fluid but has to rely on certain rules. So you must build your schema knowing your limitations and making them clear to your coworkers and customers and users.

This is the best way to go forward, because in my experience people say they would rather redesign everything and make everything dynamic, but once they go down that path, they wish they had someone to lead them back out and just show them which way is best practice. (which is great news for consultants :D)

On to my recommendation: I recommend using JSON or some other no-sql model. This way you can create arrays of values based on a certain "characteristic". Your table should also have a formula so that the program knows which characteristic to fill when the user fills in the form or an event in transaction happens. A bit like this:

price{
    price iD:XX,
    Price details:[
         Transaction variable:XX,
         Formula to get variable value during processing: XX,
         variable value from:XX,
         variable value to: XX,
         price: XX,
         currency: XX

You want to make your formulas/modules/logic for calculating values VERY clear as prices will be contested by companies who just don't want to pay due to a price discrepancy as an excuse to make it through a quarter and spend less money... trust me, its common practice. So the more transparent to your users the more power you give them to make their jobs easier and your life easier if you are the one selling in this instance.

Your currency will also be hard to get especially if using different country currencies and keeping up to date with exchange rates... but that is a story for another day.

Hope this helps, Alex