 |
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
|
|
|

July 21st, 2004, 12:41 PM
|
Authorized User
|
|
Join Date: Jun 2004
Posts: 49
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
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
|

July 21st, 2004, 01:10 PM
|
 |
Friend of Wrox
|
|
Join Date: Aug 2003
Posts: 5,407
Thanks: 0
Thanked 16 Times in 16 Posts
|
|
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.
|

July 21st, 2004, 01:25 PM
|
Authorized User
|
|
Join Date: Jun 2004
Posts: 49
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
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.
|

July 22nd, 2004, 01:30 AM
|
Friend of Wrox
|
|
Join Date: Jun 2003
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
|
|
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
|

July 22nd, 2004, 01:38 AM
|
Friend of Wrox
|
|
Join Date: Jun 2003
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
|
|
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
|

July 22nd, 2004, 01:59 AM
|
Authorized User
|
|
Join Date: Jun 2004
Posts: 49
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
Thanks Vijay -- Will try it right on -- Shailesh.
|

July 22nd, 2004, 02:48 AM
|
Authorized User
|
|
Join Date: Jun 2004
Posts: 49
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
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
|

July 22nd, 2004, 03:02 AM
|
Friend of Wrox
|
|
Join Date: Jun 2003
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
|
|
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
|

July 22nd, 2004, 03:26 AM
|
Authorized User
|
|
Join Date: Jun 2004
Posts: 49
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
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.
|

July 24th, 2004, 03:57 PM
|
Friend of Wrox
|
|
Join Date: Jun 2003
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
|
|
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
|
|
 |