Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-sqlserver > #1694 > unrolled thread

Is it good to have a price column imn OrderLine table

Back to article view | Back to comp.databases.ms-sqlserver

Contents

  Is it good to have a price column imn OrderLine table "Tony Johansson" <johansson.andersson@telia.com> - 2014-02-26 23:26 +0100
    Re: Is it good to have a price column imn OrderLine table Erland Sommarskog <esquel@sommarskog.se> - 2014-02-27 08:06 +0000
      Re: Is it good to have a price column imn OrderLine table rja.carnegie@gmail.com - 2014-02-27 03:22 -0800
        Re: Is it good to have a price column imn OrderLine table Erland Sommarskog <esquel@sommarskog.se> - 2014-02-27 11:50 +0000
    Re: Is it good to have a price column imn OrderLine table Mark D Powell <markp28665@gmail.com> - 2014-02-27 11:23 -0800

#1694 — Is it good to have a price column imn OrderLine table

We have a many to many relations between Order and Product table.
We therefore create a connection table in between and call it OrderLine.
In the product table we have for example a field called Price for a specific 
product.

In the OrderLine table we have field such as OrderId and ProductId and 
quantity.

Now to my question is it good to have a price column in the OrderLine table. 
?
If we have a price will that mean the total price per line
such as if we have ordered 3 shoes and this specific shoes cost 10 the price 
field in OrderLine would be 30

//Tony 

[toc] | [next] | [standalone]

#1695

Tony Johansson (johansson.andersson@telia.com) writes:
> Now to my question is it good to have a price column in the OrderLine
> table. ? 
> If we have a price will that mean the total price per line such as if we
> have ordered 3 shoes and this specific shoes cost 10 the price field in
> OrderLine would be 30 

All this depends on business rules, but yes, I would expect a price column
in a Orderline table. That gives the price for that specific order. The customer may have gotten a discount. And more importantly, if you change the
price for the product, you don't want that to affect old orders.

Although, in your example I would store 10 in the Price column in the
Orderline table. If it was called Amount I would store 30. But that's more of a language issue.


-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[toc] | [prev] | [next] | [standalone]

#1696

On Thursday, 27 February 2014 08:06:51 UTC, Erland Sommarskog  wrote:
> All this depends on business rules, but yes, I would
> expect a price column in a Orderline table. That gives
> the price for that specific order. The customer may have
> gotten a discount. And more importantly, if you change
> the price for the product, you don't want that to affect
> old orders.
> 
> Although, in your example I would store 10 in the Price column
> in the Orderline table. If it was called Amount I would
> store 30.  But that's more of a language issue.

The discount may be volume-dependent and also not a unit
price; for instance, oranges, $0.50 each, or 3 for $1.00.  

Another way to write that is: item, 3 oranges; number, 1.

Maybe you need to hold the user ID that input or edited 
the order line, in case one customer is getting too much
discount.

And a belated thought - well, two thoughts; one is that 
we may be doing too much of Tony's college homework;
another is that pricing is more important on the invoice 
than on the order.  Or maybe that's a language issue too...

Also, several stories on the "Not Always Right" and 
"Not Always Working" web sites are coming to mind.
e.g. <http://notalwaysright.com/listening-is-priceless/35709>

[toc] | [prev] | [next] | [standalone]

#1697

 (rja.carnegie@gmail.com) writes:
> And a belated thought - well, two thoughts; one is that 
> we may be doing too much of Tony's college homework;
> another is that pricing is more important on the invoice 
> than on the order.  Or maybe that's a language issue too...
 
But if invoice is not produced until the order is shipped, you need to 
have the price on the order line, because if you take it from the product,
you will have a bunch of angry customers on the phone. (And a few happy
customers too. But they will be silent.)


-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[toc] | [prev] | [next] | [standalone]

#1698

On Wednesday, February 26, 2014 5:26:26 PM UTC-5, Tony Johansson wrote:
> We have a many to many relations between Order and Product table.
> 
> We therefore create a connection table in between and call it OrderLine.
> 
> In the product table we have for example a field called Price for a specific 
> 
> product.
> 
> 
> In the OrderLine table we have field such as OrderId and ProductId and 
> quantity.
> 
> Now to my question is it good to have a price column in the OrderLine table. 
> 
> ?
> 
> If we have a price will that mean the total price per line
> such as if we have ordered 3 shoes and this specific shoes cost 10 the price 
> 
> field in OrderLine would be 30
> 
> //Tony

I also would expect to see both a unit price column and a quantity ordered column on the order detail line.  Totaling can be done on the invoice so it does not have to be stored on the order line item.

HTH -- Mark D Powell --

[toc] | [prev] | [standalone]

Back to top | Article view | comp.databases.ms-sqlserver

csiph-web