Why do you need an OrderID to track users before they place an order? You
already have a basketID, no?
Here's how I would do it:
CREATE TABLE [Product]
(
ProductID INT PRIMARY KEY
-- , other columns
)
GO
CREATE TABLE [Basket]
(
BasketID INT IDENTITY(1,1)
PRIMARY KEY,
CustomerID INT NULL
-- if you always know customerID
-- before the user adds an item,
-- you should make this a foreign
-- key to the Customer table
)
GO
CREATE TABLE [BasketItem]
(
BasketID INT
FOREIGN KEY
REFERENCES Basket(BasketID),
ProductID INT
FOREIGN KEY
REFERENCES Product(ProductID)
-- , other columns, e.g. quantity
)
GO
CREATE TABLE [Order]
(
OrderID INT IDENTITY(1,1),
BasketID INT
FOREIGN KEY
REFERENCES Basket(BasketID),
-- , other columns
PRIMARY KEY (OrderID, BasketID)
)
GO
Now, when a users adds an item to their basket, your ASP code looks like
this:
<%
sql = "EXEC addBasketItem " & productID & _
", " & CLng(session("basketID")) ' & ", <customerID>"
set rs = conn.execute(sql)
session("basketID") = rs(0)
%>
The stored procedure looks like this:
CREATE PROCEDURE dbo.addBasketItem
@productID INT,
@basketID INT = 0
-- , @customerID INT
-- , other parameters (e.g. quantity)
AS
BEGIN
SET NOCOUNT ON
IF @basketID = 0
(
-- if you know customerID
INSERT Basket(CustomerID) VALUES(@custmerID)
-- if you don't
INSERT Basket DEFAULT VALUES
SELECT @basketID = SCOPE_IDENTITY()
)
INSERT BasketItems
SELECT basketID = @basketID
END
GO
When you want to get the items in a person's basket:
<%
set rs = conn.execute("EXEC showMyBasket " & session("basketID"))
do while not rs.eof
... stuff
rs.movenext
loop
%>
The stored procedure:
CREATE PROCEDURE dbo.showMyBasket
@basketID INT
AS
BEGIN
SET NOCOUNT ON
SELECT p.ProductID --, p.othercolumns
, b.quantity
FROM Basket b
INNER JOIN Product p
ON b.ProductID = p.ProductID
WHERE b.basketID = @basketID
END
GO
Only when the user actually decides to place the order should you insert a
row into the orders table. All of the relationships you claim to require
before the order takes place are intact (e.g. customerID is in the basket,
assuming you know it beforehand), but aren't solidified until the point when
you actually need them to be.
Remember that your primary bottleneck is very unlikely to be whether you
choose a text session variable or an integer session variable. However
using an IDENTITY to produce your session variable is certainly going to be
more efficient, and less cumbersome, than your big string.
Also, at TechEd a couple of years ago, I had a lengthy discussion with an
engineer from Microsoft's IIS team, who told me (to my disbelief at the
time) that, everything else being equal, storing information in a database
is more efficient than keeping all that data in session variables. The
reason is that a database is optimized for storing data, while session
variables just gobble up memory. The busier your system gets, the more the
scales will tip towards database as the better performer. With a properly
tuned system, you will most likely yield better performance and more
manageable data with a database solution. In other words, test it, with
plausible real-world scenarios (not single user)... you shouldn't just opt
for session variables because you think they'll be more efficient than using
the database.
(I left out explicit declaration of clustered indexes, this will really
depend on how you are reporting against these tables and whether you are
doing more selects or inserts, etc.)
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
Ted said:
Thanks for the prompt reply Aaron. Please bear with me, as I am fairly new
to this eCommerce malarkey.
Here are the applicable relationships of my DB: -
CustomerTable ---< OrderTable ---<BasketItemsTable>---ProcuctTable
When the user adds an item to the basket, a record is created using my
UniqueID and ProductID as a composite primary key.
When the user views their basket, I use a select statement to grab all of
the basket lines from the basket table that correspond to the monstrosity,
or "unique order ID" for display.
When the user decides to place their order, I add a line to the order
table
which links: -
Customer.CustomerID to OrderTable.CustomerID
and
OrderTable.UniqueOrderID to BasketItemsTable.UniqueOrderID
If I was to use your method, I would have to create a record in the
ordertable immediately upon the user visiting the site. And if the user
didn't place an order after visiting the site, I'd have a line in the
order
table that was related to nothing, and would therefore have to set up some
procedure to remove those lines. Is this correct?
I have no idea about performance issues, but is a string session variable
less efficient than hitting the DB with a write? I am not expecting
overwhelming demand for the site. Does anyone know of any good resources
for
performance issues? Each user will have 5 session variable that I deem
necessary to keep the DB hits down to a minimum. Oh, and I'll be hosting
on
a shared server with MS SQL 2000 if that makes any difference.
Regards,
Ted.
