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.
- 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$.
- 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$)
- 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$)
- 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 is7kg 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$.
- 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?