visitor (0 QPoints)
  • FR
  • EN
  • NL
  • DE
  • ES
315 experts, 1193 registered users, 1659 questions already answered
European Experts Exchange, the very best site for high-quality IT solutions

New Improved Search!

 


05/10/2011 1h30 : Steve Jobs is dead, the father of Apple ][ is gone, we are all orphaned.

Databases :: MySql :: Please critique this code: Entering order data in a single row
By: javaQQ U.S.A.  Date: 23/06/2003 00:00:00  English  Points: 100 Status: Answered
Quality : Excellent
Please read the code below and critique it for me.
Any corrections or ways to improve the efficiency of the code greatly welcomed.

I want the database to insert the prices for the various item in an order and to
calculate the total for the order on a single row of a table.

The entry in the Pricing_Summary should look like this:
order_id | basket_number | apple_price | banana_price | cherry_price | basket_price
111-A | 1 | 0.50 | 0.40 | 0.15 | 1.05

Price_List looks like this:
item | price
apple | 0.50
banana | 0.20
cherry | 0.05

The code:

INSERT INTO Pricing_Summary ( order_id, basket_number, apple_price )
SELECT O.order_id, O.basket_number, PL.price
FROM Orders AS O, Price_List as PL
WHERE O.order_id = '111-A'
AND O.basket_number = 1
AND PL.item = 'apple

UPDATE Pricing_Summary
SET banana_price = Price_List.price * 2
WHERE Price_List.item = 'banana'

UPDATE Pricing_Summary
SET cherry_price = Price_List.price * 3
WHERE Price_List.item = 'cherry'

UPDATE Pricing_Summary
SET order_price = apple_price + banana_price + cherry_price
WHERE order_id = '111-A'

Many thanks in advance for your help.
By: VGR Date: 23/06/2003 08:55:00 English  Type : Comment
not bad.

Queries 1,2 and 3 should be fast enough, even if they could be combined

Query 4 is ok too and can't be combined in a single query with 1, 2 and 3

wher's the problem ?
By: javaQQ Date: 23/06/2003 09:07:00 English  Type : Comment
Hi, VGR;

Please show me how to combine 1, 2, and 3.

I cannot figure out how to get past the fact that they all refer to Price_List.price, on different rows,
WHERE Price_List.item = 'banana' / 'apple' / 'cherry'.

How do you handle the WHERE clause?
By: VGR Date: 23/06/2003 09:15:00 English  Type : Answer
INSERT INTO Pricing_Summary ( order_id, basket_number, apple_price,banana_price,cherry_price )
SELECT O.order_id, O.basket_number, PL.price, a.price, b.price
FROM Orders AS O, Price_List as PL,Price_List as a,Price_List as b
WHERE O.order_id = '111-A'
AND O.basket_number = 1
AND PL.item = 'apple' AND a.Item='banana' AND b.Item='cherry';
By: psadac Date: 24/06/2003 04:54:00 English  Type : Comment
very bad design :
- table names are meaningless : Pricing_Summary = orders, Price_List = products.
- every time you add a new product your table structure change.
- there's no quantity column in your orders.
- i don't know what you want to do : write it in few english words (no sql) for each query.

personnally i would do it this way :

#
# Table structure for table `orders`
#

CREATE TABLE orders (
order_id int(11) NOT NULL auto_increment,
order_no varchar(10) NOT NULL default '',
client_id int(11) NOT NULL default '0',
day date NOT NULL default '0000-00-00',
PRIMARY KEY (order_id)
) TYPE=MyISAM;

#
# Dumping data for table `orders`
#

INSERT INTO orders VALUES (1, '111-A', 1, '2003-06-24');
# --------------------------------------------------------

#
# Table structure for table `orders_details`
#

CREATE TABLE orders_details (
order_id int(11) NOT NULL default '0',
product_id int(11) NOT NULL default '0',
quantity int(11) NOT NULL default '0',
price decimal(10,2) NOT NULL default '0.00',
PRIMARY KEY (order_id,product_id)
) TYPE=MyISAM;

#
# Dumping data for table `orders_details`
#

INSERT INTO orders_details VALUES (1, 1, 2, '0.50');
INSERT INTO orders_details VALUES (1, 2, 3, '0.20');
INSERT INTO orders_details VALUES (1, 4, 1, '1.10');
# --------------------------------------------------------

#
# Table structure for table `products`
#

CREATE TABLE products (
product_id int(11) NOT NULL auto_increment,
name varchar(40) NOT NULL default '',
price decimal(10,2) NOT NULL default '0.00',
PRIMARY KEY (product_id)
) TYPE=MyISAM;

#
# Dumping data for table `products`
#

INSERT INTO products VALUES (1, 'banana', '0.50');
INSERT INTO products VALUES (2, 'apple', '0.20');
INSERT INTO products VALUES (3, 'cherry', '0.05');
INSERT INTO products VALUES (4, 'apricot', '1.10');

but i'm not sure to understand you (where is orders structure ? ). You should provide a dump of your tables to get a faster response.
i've used phpmyadmin to generate this rapidly.

Critique, you said ;-)
By: VGR Date: 24/06/2003 17:14:00 English  Type : Comment
don't be so psycho-rigid :D

people are free to name their tables the way they want ;-)
By: psadac Date: 24/06/2003 17:32:00 English  Type : Comment
VGR, of course people are free to name their tables as they wish, but giving "correct" names will help others to understand faster.
And take a look at your solution, i think there are some little mistakes in your where statement (advice from a psycho-rigid expert :o))
By: VGR Date: 24/06/2003 17:37:00 English  Type : Comment
I only see two capital 'I' in Item in stead of 'item' all-lowercase

are those the "little mistakes" you dare to mention ? :D
By: VGR Date: 24/06/2003 17:39:00 English  Type : Comment
and I think your comment "very bad design" is not appropriate for describing a layout that, to my humble knowledge, is correct. Only the tables' names could be changed - but they don't ***have to ***

The Asker wrote "please criticize my layout", not "please criticize my naming rule" :-)


Moreover, changing tables'names won't enhance performance, which is the second part of the Question :D
By: javaQQ Date: 24/06/2003 18:11:00 English  Type : Comment
Thanks for your attention to the question.
I will award the points to VGR, who helped me improve the existing code.

Thanks also to psadac. Your contribution gives me something to think about.
I expect that my overall design for the databases is unorthodox, but I will have to revisit that later.
Right now the priority is on getting something up and running.

Many thanks.
By: psadac Date: 25/06/2003 04:14:00 English  Type : Comment
VGR, i think naming rules or performance are not the most important thing : if you have to modify your database schema each time you add a new product there MUST be something wrong.
additionnally i think "I will have to revisit that later" is not a good solution : you will have to modify your programs and import your existing data into your new tables.
However you're right on one point, you have not made any mistake, i just had a problem with table names ;-)

Do register to be able to answer

©2010 These pages are served without commercial sponsorship. (No popup ads, etc...). Bandwidth abuse increases hosting cost forcing sponsorship or shutdown. This server aggressively defends against automated copying for any reason including offline viewing, duplication, etc... Please respect this requirement and DO NOT RIP THIS SITE.
Please DO link to this page! Post to Twitter Post to Plurk Post to Yahoo Buzz Post to Delicious Post to Digg Post to Facebook Post to MySpace Post to Ping.fm Post to Reddit Post to StumbleUpon

EContact
browser fav
page generated in 312.933920 milliseconds

Why Google AdSense ads ?
compteur
 Ranking-Hits PageRank for this page