Wrox Programmer Forums
|
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old July 21st, 2004, 12:41 PM
Authorized User
 
Join Date: Jun 2004
Posts: 49
Thanks: 0
Thanked 0 Times in 0 Posts
Default Adding optional columns?

Folks - I need some database ideas/suggestions. Here is the scenario -- in a particulart type of product page, say shoes, the users have to select color, size, and width. But for the vast majority of other products color, size, and width does not apply.
As it is now, I have set the color, size, and width crietria in my stored procedure as optional, which is how I think it should be. However, since the primary key used for adding to the shopping cart datagrid is the productID, this is what happens:
when the user selects the same ****************/product but with different size, color, and width, it does not add the different size, color, and width parameters to the shopping cart datagrid but simply updates the quantity of the product because it has the same productID.
Is is possible to add the product with the same productID but in a different row if the color, size, and width criteria have changed? I imagine some drastic changes might need to be made to the database -- but I really do not know where to start -- please throw some suggestions my way. Thanks.
____________________________
Stored Procedure used to add item:



CREATE Procedure ShoppingCartAddItem
(
    @CartID nvarchar(50),
    @ProductID int,
    @Quantity int,
    @Color nvarchar(10) = null,
    @Size nvarchar(10) = null,
    @Width nvarchar(10) = null
)
As

IF(RTRIM(LTRIM(@Color))) = null SELECT @Color = null
IF(RTRIM(LTRIM(@Size))) = null SELECT @Size = null
IF(RTRIM(LTRIM(@Width))) = null SELECT @Width = null

DECLARE @CountItems int

SELECT
    @CountItems = Count(ProductID)
FROM
    ShoppingCart
WHERE

    CartID = @CartID

IF @CountItems > 0 /* There are items - update the current quantity */

    UPDATE
        ShoppingCart
    SET
        Quantity = (@Quantity + ShoppingCart.Quantity)
    WHERE

        CartID = @CartID

ELSE /* New entry for this Cart. Add a new record */

    INSERT INTO ShoppingCart
    (
        CartID,
        ProductID,
        Quantity,
    Color,
    Size,
    Width
    )
    VALUES
    (
        @CartID,
        @ProductID,
        @Quantity,
    @Color,
    @Size,
    @Width
    )
GO
 
Old July 21st, 2004, 01:10 PM
planoie's Avatar
Friend of Wrox
 
Join Date: Aug 2003
Posts: 5,407
Thanks: 0
Thanked 16 Times in 16 Posts
Default

From an architecture perspective, if you have a product that can have different characteristics then technically it should be a different product. This basically implies that you need to change your database design for products.

Maybe a slightly easier solution would be to add an "options" column to the ShoppingCart table. The unique key to the ShoppingCart table would then be the product ID and the options. The options field need only be a list of the options. This provides the flexibility to support different option sets. Then your logic for adding an item wouldn't have to change too much. You'd just need to include a check for the same options AND productID.
 
Old July 21st, 2004, 01:25 PM
Authorized User
 
Join Date: Jun 2004
Posts: 49
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi Peter -- Thanks. Never thought I would hear from a moderator without doing something bad! I think I will follow the second option -- I already have those columns in the shopping cart table but they are currently nullable -- I guess I will have to give them a default value and make them non-nullable. my concern at this point is that I also have a quite a few tables and procedures that use productID as either a primary key or foreign key, for instance the ordersDetail table. Well, we'll see. Thanks again. Shailesh.

 
Old July 22nd, 2004, 01:30 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

Hi Shailesh,

Code:
CREATE Procedure ShoppingCartAddItem
(
    @CartID nvarchar(50),
    @ProductID int,
    @Quantity int,
    @Color nvarchar(10) = null,
    @Size nvarchar(10) = null,
    @Width nvarchar(10) = null
)
As

-- Size, Color and Width are already NULL if no values passed. So the following is not required.
/*IF(RTRIM(LTRIM(@Color))) = null SELECT @Color = null
IF(RTRIM(LTRIM(@Size))) = null SELECT @Size = null
IF(RTRIM(LTRIM(@Width))) = null SELECT @Width = null*/

DECLARE @CountItems int
SELECT @CountItems = Count(*)
FROM ShoppingCart
WHERE CartID = @CartID and ProductId = @ProductID 
    and Color = @Color and Size = @Size and Width = @Width

IF @CountItems > 0  /* There are items - update the current quantity */

    UPDATE
        ShoppingCart
    SET 
        Quantity = (@Quantity + ShoppingCart.Quantity)
    WHERE CartID = @CartID and ProductId = @ProductID 
        and Color = @Color and Size = @Size and Width = @Width

ELSE  /* New entry for this Cart.  Add a new record */

    INSERT INTO ShoppingCart
    (
        CartID,
        ProductID,
        Quantity,
        Color,
        Size,
        Width
    )
    VALUES
    (
        @CartID,
        @ProductID,
        @Quantity,
        @Color,
        @Size,
        @Width
    )
GO
This should work out for you.
Cheers!

_________________________
- Vijay G
Strive for Perfection
 
Old July 22nd, 2004, 01:38 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

In addition to Peter's suggestion, I would say...

You can move these columns(Color, Size, Width) out of SHOPPINGCART table to another table called OPTIONS and use its key field OptionsID as a relation with Shopping Cart table.

Shopping Cart Table - all your others columns, OptionsId (If I am not wrong, as Peter said, the combination of ProductId and OptionsID makes one Item added to the CART)

Options Table - Color, Size, Width and OPTIONSID

I wouldn't say you have those 3 columns in shopping cart as nullable when one adds some product other than shoes, where not all records there would related to SHOES and that way, you would have columns with values = NULL here and there, that doesn't conform normalisation. If very less effort required and not much expected to do alterations, I would say better move out those options to a different table and when no options required for a product, have its OPTIONID as 1 or ZERO or somethingelse fixed.

Hope that helps.
Cheers!

_________________________
- Vijay G
Strive for Perfection
 
Old July 22nd, 2004, 01:59 AM
Authorized User
 
Join Date: Jun 2004
Posts: 49
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks Vijay -- Will try it right on -- Shailesh.

 
Old July 22nd, 2004, 02:48 AM
Authorized User
 
Join Date: Jun 2004
Posts: 49
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi Vijay -- there seems to be a problem here : I have been able to add products to the shopping cart grid with different color, size, and width options for the same product. However, when I place the order I get the following error -- which I was expecting:
Violation of PRIMARY KEY constraint 'PK_OrderDetails'. Cannot insert duplicate key in object 'OrderDetails'. The statement has been terminated.
___
this is because the productID is a primary key along with OrderID for the OrderDetails table -- so obviously it cannot get around adding rows in the OrderDetails table with the same ProductID AND OrderID. Should I go ahead and add another column to the OrderDetails table, something like OrderDetailsID and make it identity increment and then make a composite primary key with OrderDetailsID, ProductID, and OrderID. What do you think? Here is my OrdersAdd sproc:



CREATE Procedure OrdersAdd
(
    @CustomerID int,
    @CartID nvarchar(50),
    @OrderDate datetime,
    @ShipDate datetime =null,
    @OrderID int OUTPUT
)
AS

BEGIN TRAN AddOrder

/* Create the Order header */
INSERT INTO Orders
(
    CustomerID,
     OrderDate,
    ShipDate,
    Status
)
VALUES
(
    @CustomerID,
    @OrderDate,
    @ShipDate,
    0
)

SELECT
        @OrderID = @@Identity

FROM Orders
INNER JOIN Customers on Orders.CustomerID = Customers.CustomerID

/* Copy items from given shopping cart to OrdersDetail table for given OrderID*/
INSERT INTO OrderDetails
(
    OrderID,
    ProductID,
    Quantity,
    UnitCost,
    ProductName,
    Color,
    Size,
    Width
)

SELECT
    @OrderID,
    ShoppingCart.ProductID,
    Quantity,
    Products.UnitCost,
    Products.ProductName,
    ShoppingCart.Color,
    ShoppingCart.Size,
    ShoppingCart.Width


FROM
    ShoppingCart
  INNER JOIN Products ON ShoppingCart.ProductID = Products.ProductID

WHERE
    CartID = @CartID

/* Removal of items from user's shopping cart will happen on the business layer*/
EXEC ShoppingCartEmpty @CartId

COMMIT TRAN AddOrder
GO
__________
Thanks again for your help. Shailesh

 
Old July 22nd, 2004, 03:02 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

Yes Shailesh, that makes sense. OrderDetailsID can be INCREMENTED by 1 in this SP itself or be it an IDENTITY column. But in that case you don't have to make that one among composite keys along with ORDERID and PRODUCTID there. It can be made a PrimaryKey of that table and can remove the COMPOSITE behaviour of ORDERID and PRODUCTID from there.

ELSE

Do you have something like ITEMNUMBER in the SHOPPINGCART table? If so you can go ahead and add that in ORDERDETAILS table and make that COMPOSITE key along with PRODUCTID and ORDERID. Or even OPTIONS of SHOPPINGCART table can be used instead of ITEMNUMBER, if you don't have it there. But I have a feeling of duplicating things from SHOPPINGCART table to ORDERDETAILS table.

So, I believe that the first one would be better.

Hope that helps.
Cheers!

_________________________
- Vijay G
Strive for Perfection
 
Old July 22nd, 2004, 03:26 AM
Authorized User
 
Join Date: Jun 2004
Posts: 49
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Vijay, I did as I had planned immediately after I posted my last reply. It seems to be working. Can I leave my orderdetails as it is -- it seems to be working -- with the three composite columns as PK (OrderDetailsID, OrderID, ProductID) or are you saying that I remove OrderID, and ProductID from the primary key? What your are saying making sense to me -- just want to be sure.

If you remember I post a topic few days ago about inserting duplicating rows -- I had tried something like you guys had suggested and it was adding separate rows in the shopping cart for the same **************** but with different options -- which is exactly what i wanted. But then for some reason I have not been able to figure out -- It started inserting two rows into my orders table for every single order, of course with different PKs. I never resolved that problem -- just started all over again -- actually created a new solution file in VS 2003 from scratch. I hope it won't happen this time.

But again do I really need to take the OrderID and ProductID from the OrderDetails table composite primary key or can I leave it like that.
You have been a great help. Shailesh.

 
Old July 24th, 2004, 03:57 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

The reason why I didn't want to have OrderDetailsID as one of the composite primary keys is ...
Why does one have to use a primary key? The column that contains non-null and unique value that uniquely identifies a row from others, which can also be used as referencing column with another table can be called so. In your case, before you added OrderDetailsID column to that table, every row wasn't unique as there could be many rows with same ORDERIDs and same PRODUCTIDs(but different size/color/width which wasn't part of Composite key). So the other factors that could make every row different from the other could be the OPTIONs (if you have any in that table = size, color and width) as Peter suggested. If not, add another column like OrderDetailsID. But one would never have to use that as one of the COMPOSITE key columns, as that column itself has unique values for every row by which a row can be differentiated from the other. So there is no necessity to add that to the composite key.

But remember, only if SIZE, COLOR and WIDTH changes, you should add that as different entry like that you had in shopping cart.

Hope that helps.
Cheers!

_________________________
- Vijay G
Strive for Perfection





Similar Threads
Thread Thread Starter Forum Replies Last Post
vba question on adding certain columns csilla Excel VBA 0 July 30th, 2008 05:25 AM
Adding columns to Gridview gagansharma7 ADO.NET 1 February 15th, 2008 10:54 AM
Optional prameters and C#? NewTitle2007 BOOK: ASP.NET 2.0 Instant Results ISBN: 978-0-471-74951-6 2 August 5th, 2007 12:17 PM
problem adding two columns g_vamsi_krish SQL Server 2000 2 March 6th, 2006 10:56 AM
adding columns to matrix [email protected] BOOK: Professional SQL Server Reporting Services ISBN: 0-7645-6878-7 0 May 4th, 2005 07:05 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.