locked
Should I have a table column with total price? RRS feed

  • Question

  • Hello,

    I have the following SQL Scheme:

        create table dbo.Baskets (
          Id int not null primary key clustered (Id),
          Name nvarchar (80) not null
        )
    
        create table dbo.Products (
          Id int not null primary key clustered (Id),
          BasketId int not null,
          Price decimal (10, 2) not null,
         Name nvarchar (80) not null
        )


    So one basket can have many products.


    I often need to find baskets in a specific price range.

    I know the basket price is the sum of its products prices.

    **QUESTIONS**

    1. Should I add a column to Baskets with TotalPrice?

    2. Would this improve query performance a lot?

    3. Can this be done automatically when Products are updated?

    4. Should I index some column here?

    Thank You,

    Miguel

    • Edited by MDMoura Wednesday, September 17, 2014 8:49 AM
    Wednesday, September 17, 2014 8:49 AM

Answers

  • You shouldn't need to put a total column on the baskets table. In fact, it is not good practice to do so.  You should not have to store (and keep synchronized) values that can be easily derived.

    Create a clustered index on Products (BasketID).  

    You don't have a quantity on Products?  

    Can a user only by one or do you have multiple records for the same product in your basket?

    • Proposed as answer by George Robertson Jr Wednesday, September 17, 2014 9:21 AM
    • Marked as answer by MDMoura Thursday, September 25, 2014 9:12 AM
    Wednesday, September 17, 2014 9:21 AM
  • No million of baskets here ... Much less ... A few hundreds at the most.

    Then I would calculate the total on the fly.

        create table dbo.Baskets (
          Id int not null primary key clustered (Id),
          Name nvarchar (80) not null
        )
    
        create table dbo.Products (
          Id int not null primary key clustered (Id),
          BasketId int not null,
          Price decimal (10, 2) not null,
         Name nvarchar (80) not null,
         Quantity int not null
        )
    select b.ID, b.Name,
    (select sum(Price * Quantity) 
    from dbo.Products p where p.BasketID = b.ID) as TotalPrice
    from dbo.Baskets b
    

    Or

    select b.ID, b.Name
    ,sum(Price * Quantity)  as TotalPrice
    from dbo.Baskets b
    inner join dbo.Product p
    on p.BasketID = b.ID 
    group by b.ID, b.Name


    Russel Loski, MCT, MCSE Data Platform/Business Intelligence. Twitter: @sqlmovers; blog: www.sqlmovers.com

    • Marked as answer by Elvis Long Thursday, September 25, 2014 5:23 AM
    Wednesday, September 17, 2014 10:49 AM

All replies

  • You shouldn't need to put a total column on the baskets table. In fact, it is not good practice to do so.  You should not have to store (and keep synchronized) values that can be easily derived.

    Create a clustered index on Products (BasketID).  

    You don't have a quantity on Products?  

    Can a user only by one or do you have multiple records for the same product in your basket?

    • Proposed as answer by George Robertson Jr Wednesday, September 17, 2014 9:21 AM
    • Marked as answer by MDMoura Thursday, September 25, 2014 9:12 AM
    Wednesday, September 17, 2014 9:21 AM
  • You can calculate it from products table itself. So no need of adding it to Basket table and introducing redundancy.

    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Wednesday, September 17, 2014 9:34 AM
  • 1.It depends on the queries run against the table 

    2.May or may not, depends how would like to calculate it or you get already calculated value for the client or whatever

    3. Yes, but then you will have to write a trigger at least

    4. Depends on the queries and columns  that participate in WHERE clause


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Wednesday, September 17, 2014 9:52 AM
    Answerer
  • Create a clustered index on Products (BasketID).  

    You don't have a quantity on Products?

    You mean the following:

        create index IX_Products_BasketId on Products (BasketId);

    Should I add clustered? Why the index should be clustered?

    Yes, I do have a quantity on Products. I just forgot to add it when posting my question.

    Wednesday, September 17, 2014 9:54 AM
  • If you have millions of baskets (not thousands) the time saved by putting the total in the Baskets table could be significant.  But there will be significant complexity, in that you will need some means to keep the total up to date.  A trigger will do it automatically. Or you can build stored procedures to insert update and delete products that will also update the Baskets when products change (this is not automatic).


    Russel Loski, MCT, MCSE Data Platform/Business Intelligence. Twitter: @sqlmovers; blog: www.sqlmovers.com

    Wednesday, September 17, 2014 10:02 AM
  • No million of baskets here ... Much less ... A few hundreds at the most.

    Wednesday, September 17, 2014 10:10 AM
  • No million of baskets here ... Much less ... A few hundreds at the most.

    Then I would calculate the total on the fly.

        create table dbo.Baskets (
          Id int not null primary key clustered (Id),
          Name nvarchar (80) not null
        )
    
        create table dbo.Products (
          Id int not null primary key clustered (Id),
          BasketId int not null,
          Price decimal (10, 2) not null,
         Name nvarchar (80) not null,
         Quantity int not null
        )
    select b.ID, b.Name,
    (select sum(Price * Quantity) 
    from dbo.Products p where p.BasketID = b.ID) as TotalPrice
    from dbo.Baskets b
    

    Or

    select b.ID, b.Name
    ,sum(Price * Quantity)  as TotalPrice
    from dbo.Baskets b
    inner join dbo.Product p
    on p.BasketID = b.ID 
    group by b.ID, b.Name


    Russel Loski, MCT, MCSE Data Platform/Business Intelligence. Twitter: @sqlmovers; blog: www.sqlmovers.com

    • Marked as answer by Elvis Long Thursday, September 25, 2014 5:23 AM
    Wednesday, September 17, 2014 10:49 AM