Query design for tracking order status

Question

I have the following tables in MySQL

`orders` (
  `orderID` int(10) unsigned NOT NULL auto_increment,
  `agentID` int(10) unsigned NOT NULL,
  `order_date` datetime NOT NULL,
  `senderID` int(10) unsigned NOT NULL,
  `receiverID` int(10) unsigned NOT NULL,
  PRIMARY KEY  (`orderID`)
)

 `order_details` (
  `order_details_id` int(10) unsigned NOT NULL auto_increment,
  `orderID` int(10) unsigned NOT NULL,
  `packageID` int(10) unsigned NOT NULL,
   PRIMARY KEY  (`order_details_id`),
   KEY `fk_orderID` (`orderID`)
) 

`order_status` (
  `status_id` int(10) unsigned NOT NULL auto_increment,
  `order_details_id` int(10) unsigned NOT NULL,
  `descrID` int(10) unsigned NOT NULL,
  `date_time` datetime NOT NULL,
  PRIMARY KEY  (`status_id`),
  KEY `theDate` (`date_time`),
  KEY `stat` (`descrID`,`order_details_id`,`date_time`)
)

 `order_statuses` (
  `descrID` int(10) unsigned NOT NULL auto_increment,
  `descr` varchar(255) collate utf8_bin NOT NULL,
  `public` smallint(6) NOT NULL COMMENT '1 or 0',
  `leg` tinyint(4) NOT NULL COMMENT 'carrier code',
  PRIMARY KEY  (`statusID`)
)

The third and fourth tables are used to keep track of all the stages the orders go through. The flow is as follows: an order is placed at an agent of the company, the agent gives the order to a carrier, the carrier transfers the order to a hub owned by the company, another carrier picks it up and then hopefully delivers it. 2 questions;

First, any comments on the design?

Second, I need to run a query that will give me the all the orders based on the specific LATEST status per carrier. Right now the query that I am using is taking about 1.5 seconds to complete and I only have one row in the tables.

select odd.order_details_id
from order_details odd, orders od
where odd.orderID = od.orderID and (
    select oas.statusID
    from order_status oas, order_statuses oss
    where oas.descrID = oss.descrID and oss.leg=1 and odd.order_details_id =oas.order_details_id
    order by oas.date_time desc limit 1)=2

Is there a way to optimize this? Thanks

Answer 1

Use a composite key on order_statuses(descrID, leg).

Denormalize the order_details table to store the latest order_status.status_id (maintained by a trigger).

Use smaller integer values based on the ranges - does agent_id need to be an unsigned int?

Answer 2

A good start.

1. use InnoDB (or better yet, Postgres). You can use MyISAM slaves if you want full-text search, but otherwise, foreign keys are your friends.

2. watch your under_scores and camelCase. Use lowercase + underscores for max compatibility.

3. An order is sold by a vendor to a customer and is paid for by the bill_to party (who is often the customer). Can someone pay for the order that is not the customer? (ex. Grandma buys for Grandson, but uses Grandpas credit card). Don't forget the bill_to_address, that is often required.

4. order_details is usually called order_items. An order_item can be shipped to someone that is different from the customer or bill_to party. You could add ship_to and ship_to_address fields on order_details.

5. Are you selling only packages, or might you sell other goods, services, etc? Look into Table Inheritance.

6. Different parties play different roles in an order, in particular split sales commission. see def below

7. Can an order have the same status more than once? If not, remove order_status.status_id

8. I would rename order_statuses to order_status_types

Good luck!

order_role (table)
order_id FK 
party_id FK 
role_type FK order_role_type
commission_percent null