Possible unnecessary double iteration
Posted: Thu Jun 26, 2008 6:51 am
Picture this: I've got an orders table and an orders description table.
The order_lsits table contains all the details of each order.
The price for each menu_item is kept in the menu_items table, so originally I thought it would be fine for working out total prices with a couple of joins. Then I realised that if I wanted to look at how much money I'd made last week, and I'd put all the prices of the menu items up by a £1 since then, it would give misleading results. So I figured it would be a better idea to log the total prices for each order as they were received.
Now to the problem. At the moment I insert a new row into the orders table first, then iterate through each menu item on the order, inserting them into order_lists using LAST_INSERT_ID() - this doesn't take into account the total_price property.
So, now, the only method I can think of to accomplish this properly is to iterate through all the menu items on the order (and their quantities) fetch their prices (multiply them by quantity) and work out the total price. Then run the insert query for the orders table. Then iterate through all the menu items on the order again for the order_lists table.
Surely there's a better method than this?
Thanks for any input.
EDIT: Just to mention a solution to the problem which I think is infeasible is to keep a menu_item price change log and scrap the total_price field in the orders table. Then everytime a menu item has it's price changed the old price is stored along with the ID of the item and the date/time in the change log. This way I could check if menu items prices had changed before working out how much the order acdtually came to. This would just create an over complex set of queries though.
EDIT(2): OK, I've had a rethink. I don't think this is the best way to solve it but it's the best I can think of. I've decided to change the table structures to:
So basically all I'm doing now is storing the menu_item's price * quantity ordered in the order_lists table. Then if I ever want to fetch the order total price I can do a simple query using SUM(). I came to this decision because I couldn't work out how to write a query to insert the correct total price of a whole order into the orders table given the menu item id's and the quantities. If there was no notion of quantity it would be OK.
Another idea I almost went with would be to calculate the total price of the whole order using a SELECT query for each menu item on the order during the same loop that INSERTs into the order_lists table. Then I INSERT into the orders table afterwards, but doing this would mean require (different menu items * 2) + 1 total queries (and I would have to use LAST_INSERT_ID() + 1 when referencing the un-entered order).
Any better sugestions will be welcomed with open arms!
Code: Select all
orders:
+--------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+----------------+
| order_id | int(5) | NO | PRI | NULL | auto_increment |
| table_id | int(5) | NO | | NULL | |
| total_price | decimal(6,2) | NO | | NULL | |
| time_ordered | datetime | NO | | NULL | |
| completed | char(1) | NO | | N | |
+--------------+--------------+------+-----+---------+----------------+
Code: Select all
order_lists:
+--------------+--------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+--------+------+-----+---------+-------+
| order_id | int(5) | NO | PRI | NULL | |
| menu_item_id | int(5) | NO | PRI | NULL | |
| quantity | int(5) | NO | | NULL | |
+--------------+--------+------+-----+---------+-------+
The price for each menu_item is kept in the menu_items table, so originally I thought it would be fine for working out total prices with a couple of joins. Then I realised that if I wanted to look at how much money I'd made last week, and I'd put all the prices of the menu items up by a £1 since then, it would give misleading results. So I figured it would be a better idea to log the total prices for each order as they were received.
Now to the problem. At the moment I insert a new row into the orders table first, then iterate through each menu item on the order, inserting them into order_lists using LAST_INSERT_ID() - this doesn't take into account the total_price property.
So, now, the only method I can think of to accomplish this properly is to iterate through all the menu items on the order (and their quantities) fetch their prices (multiply them by quantity) and work out the total price. Then run the insert query for the orders table. Then iterate through all the menu items on the order again for the order_lists table.
Surely there's a better method than this?
Thanks for any input.
EDIT: Just to mention a solution to the problem which I think is infeasible is to keep a menu_item price change log and scrap the total_price field in the orders table. Then everytime a menu item has it's price changed the old price is stored along with the ID of the item and the date/time in the change log. This way I could check if menu items prices had changed before working out how much the order acdtually came to. This would just create an over complex set of queries though.
EDIT(2): OK, I've had a rethink. I don't think this is the best way to solve it but it's the best I can think of. I've decided to change the table structures to:
Code: Select all
mysql> DESCRIBE `restaurant`.`orders`;
+--------------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+----------+------+-----+---------+----------------+
| order_id | int(5) | NO | PRI | NULL | auto_increment |
| table_id | int(5) | NO | | NULL | |
| time_ordered | datetime | NO | | NULL | |
| completed | char(1) | NO | | N | |
+--------------+----------+------+-----+---------+----------------+
mysql> DESCRIBE `restaurant`.`order_lists`;
+--------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+-------+
| order_id | int(5) | NO | PRI | NULL | |
| menu_item_id | int(5) | NO | PRI | NULL | |
| quantity | int(5) | NO | | NULL | |
| total_price | decimal(6,2) | NO | | NULL | |
+--------------+--------------+------+-----+---------+-------+Another idea I almost went with would be to calculate the total price of the whole order using a SELECT query for each menu item on the order during the same loop that INSERTs into the order_lists table. Then I INSERT into the orders table afterwards, but doing this would mean require (different menu items * 2) + 1 total queries (and I would have to use LAST_INSERT_ID() + 1 when referencing the un-entered order).
Any better sugestions will be welcomed with open arms!