Data Model : Order Routing table designs
- Saurav Kumar Ram
What is the purpose of this article?
This is a sub document of order routing main document.Order Routing Implementation Document
The purpose of this article is to decide the configurations of the new tables which needs to be added in WMS DB to achieve order routing feature
Database used : MYSQL
Database staging : eshopbox_client_portal_dev
Database prod : eshopbox_wms_production
Tables to Be added
order_routing_rule_fileds - This table will be used for storing the details required to create an order routing rule by the user in UI
order_routing_rules - This table will be used for storing the rules created by by the user in UI
Table Configurations
Table Name: order_routing_rule_fileds
column name | description | type | unsigned | key (primary/foreign) | null-type | default value (if any) |
|---|---|---|---|---|---|---|
id | Primary key of the table | int(11) AI (auto-increment) | Yes | primary | not null |
|
field |
| varchar(50) | Yes | UNIQUE | not null |
|
condition |
| varchar(50) |
|
| not null |
|
value | Values of data saved in field column | text |
|
|
|
|
created_at | Creation date-time | timestamp |
|
| not null | CURRENT_TIMESTAMP |
updated_at | Updation date-time | timestamp |
|
| not null | CURRENT_TIMESTAMP |
id | field | condition | value | created_at | updated_at |
|---|---|---|---|---|---|
1 | Sales channel | is/is not | national/local/zonal |
|
|
2 | Payment method | is/is not | prepaid/COD |
|
|
3 | Shipping state | is/is not | Andaman & Nicobar Islands/Andhra Pradesh/Arunachal Pradesh/Assam/Bihar/Chandigarh/Chhattisgarh/Daman & Diu/Delhi/Goa/Gujarat/Haryana/Himachal Pradesh/Jammu & Kashmir/Jharkhand/Karnataka/Kerala/Lakshadweep/Madhya Pradesh/Maharashtra/Manipur/Meghalaya/Mizoram/Nagaland/Odisha/Pondicherry/Punjab/Rajasthan/Sikkim/Tamil Nadu/Telangana/Tripura/Uttar Pradesh/Uttarakhand/West Bengal |
|
|
4 | Shipping pincodes | is/is not | (NULL) |
|
|
5 | Order having vertical | contains/not contains | (NULL) |
|
|
6 | Order having SKU | contains/not contains | (NULL) |
|
|
7 | Order having brand | contains/not contains | (NULL) |
|
|
8 | Customer shipping preference | is/is not | (NULL) |
|
|
9 | Max items in the order | is/is not | (NULL) |
|
|
10 | Max weight of the order | is/is not | (NULL) |
|
|
Table Name : order_routing_rules
column name | description | type | unsigned | key (primary/foreign) | null-type | default value (if any) |
|---|---|---|---|---|---|---|
id | Primary key of the table | int(11) AI (auto-increment) | Yes | primary | not null |
|
account_id | Primary key of accounts table | int(11) | Yes | foreign key | not null |
|
name | Name of the rule | varchar(50) |
|
| not null |
|
rules | Rule created by user | text |
|
|
|
|
priority | Priority of the rule | int |
|
|
|
|
shipping_methods | Shipping methods priority wise | text |
|
|
|
|
is_default | Defines whether it is the default rule or not | enum('0','1') |
|
|
| 0 |
status | Whether this rule is active or not | enum('0','1') |
|
|
| 1 |
isOrderSplittingEnabled | Defines whether splitting is enabled in this rule or not | enum('0','1') |
|
|
| 1 |
created_by | Who created this rule |
|
|
|
|
|
updated_by | Who updated this rule latest |
|
|
|
|
|
created_at | Creation date-time | timestamp |
|
| not null | CURRENT_TIMESTAMP |
updated_at | Updation date-time | timestamp |
|
| not null | CURRENT_TIMESTAMP |