August 8, 2009 at 3:14 pm
I have a database that has a product table.
This database also has a Quote table. For the Quote table the shopper can request a quote by selecting a product and inserting the productID into the Quote table.
This Quote table allows for back and forth negation of pricing and quote / product related questions. The Quote table is designed to be for bulk pricing and not ones and twos. If the shopper wants to by one or two small items they can just add that to the cart and check out.
Here is my issue.
I am to the part where they user can pull their order history, and this can be done be doing a Select OrderID against the Order table by the @ShopperID.
Ok, now we have the OrderID and lets get all of the OrderDetail for this OrderID.
My productID column in my OrderDetail table is EITHER from the Quote table or the Product table.
Question:
In my SELECT against the OrderDetail table how should I check to see if the productID is from the Product table or if the productID is from the Quote table?
-----------------------------------------------------------------
CREATE TABLE [dbo].[utbProduct](
[ProductID] [int] IDENTITY(3423,1) NOT NULL,
[Name] [varchar](50) NOT NULL,
[Title] [varchar](255) NOT NULL,
[BaseCost] [money] NOT NULL,
CONSTRAINT [PK_utbProduct] PRIMARY KEY CLUSTERED
(
[ProductID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[utbProduct] ADD DEFAULT ((0.00)) FOR [BaseCost]
GO
-----------------------------------------
CREATE TABLE [dbo].[utbQuote](
[QuoteID] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](30) NOT NULL,
[ProductID] [int] NOT NULL,
[FileID] [int] NOT NULL,
[NoteTo] [varchar](1500) NOT NULL,
[NoteFrom] [varchar](1500) NOT NULL,
[Cost] [money] NOT NULL,
[RequestDate] [datetime] NOT NULL,
[ModifiedDate] [datetime] NOT NULL,
[ShipToZipcode] [int] NOT NULL,
[Pending] [bit] NOT NULL,
[ShopperAccepted] [bit] NOT NULL,
[EstimatorAccepted] [bit] NOT NULL,
[ShopperID] [uniqueidentifier] NOT NULL,
[Quantity] [int] NOT NULL,
[ShopperReview] [bit] NOT NULL,
[Paid] [bit] NOT NULL,
[SizeID] [int] NOT NULL,
[TypeID] [int] NOT NULL,
[StyleID] [int] NOT NULL,
[LengthID] [int] NOT NULL,
CONSTRAINT [PK_utbQuote_QuoteID] PRIMARY KEY CLUSTERED
(
[QuoteID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[utbQuote] WITH CHECK ADD CONSTRAINT [FK_utbQuote_utbProduct_ProductID] FOREIGN KEY([ProductID])
REFERENCES [dbo].[utbProduct] ([ProductID])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
-----------------------------------------
CREATE TABLE [dbo].[utbOrderDetail](
[OrderDetailID] [int] IDENTITY(1,1) NOT NULL,
[OrderID] [int] NOT NULL,
[ProductID] [int] NOT NULL,
[Quantity] [tinyint] NOT NULL,
[Cost] [money] NOT NULL,
[ModifiedDate] [datetime] NOT NULL,
[SizeID] [int] NOT NULL,
[TypeID] [int] NOT NULL,
[StyleID] [int] NOT NULL,
[LengthID] [int] NOT NULL,
CONSTRAINT [PK_OrderDetailID] PRIMARY KEY CLUSTERED
(
[OrderDetailID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[utbOrderDetail] WITH NOCHECK ADD CONSTRAINT [FK_utbOrderDetail_utbProduct_ProductID] FOREIGN KEY([ProductID])
REFERENCES [dbo].[utbProduct] ([ProductID])
GO
ALTER TABLE [dbo].[utbOrderDetail] NOCHECK CONSTRAINT [FK_utbOrderDetail_utbProduct_ProductID]
GO
Dam again!
August 8, 2009 at 3:18 pm
I just noticed that my Quote table did not have the foriegn key to the OrderDetail table.
I have the Quote table with a foreign key to the OrderDetail table in my database I must of left the script out when creating th script.
***I do not have the foreign keys set up as Constraints from the Product table or the Quote table.
Dam again!
August 10, 2009 at 12:23 am
I have'nt looked deep into this but will a column in the order detail table indicating that the product is from the quote or product table help.
"Keep Trying"
August 10, 2009 at 2:12 pm
I'd suggest that you always get your Product information (name, etc...) from the Products table and then include a LEFT OUTER JOIN to the Quotes table to get quote information. My query would be something like this:
/*
I assume that the Cost in the Quote and
BaseCost for the product are per item
price and the OrderDetail cost it a
total cost. The Left Join gets you the
Quote information when it's there and
NULL's when it's not.
*/
SELECT
OD.OrderID,
OD.OrderDetailID,
OD.Quantity,
OD.Cost,
P.ProductId,
P.NAME AS ProductName,
P.Title,
ISNULL(Q.Cost, P.BaseCost) AS Cost,
Q.QuoteID,
Q.NAME AS [QuoteName],
Q.RequestDate,
Q.Quantity
FROM
dbo.utbOrderDetail OD JOIN
dbo.utbProduct P ON
OD.ProductId = P.ProductId LEFT JOIN
dbo.utbQuote Q ON
P.ProductId = Q.ProductId
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 10, 2009 at 3:37 pm
What a great piece of code and a great way to implement this...
Keeps me from having to do a IsQuote BIT column...
Thanks for the professional help !
Erik
Dam again!
August 10, 2009 at 3:38 pm
Yes,
The line item in the OrderDetail table on column ProductID can represent either the FK_ from the utbQuote table or the utbProduct table.
Dam again!
August 11, 2009 at 5:20 pm
AFCC Inc. Com (8/10/2009)
Yes,The line item in the OrderDetail table on column ProductID can represent either the FK_ from the utbQuote table or the utbProduct table.
The OrderDetial.ProductID should be a FK to Product and Quote.ProductID should be a FK to Product. That way you can actuall enforce the relationships correctly.
August 18, 2009 at 12:35 am
I know this is going to sound crazy, but that Quote table will turn into it's on product table after the shopper request a quote. The QuoteID is the ProductID in the OrderDetail table and ProductID is the ProductID from the Product Table. So the Quote table is acting as the product table if the shopper chooses to get a quote and not just do a buy now.
I know it sounds crazy; however, that quote can be twisted so many ways to the point that I just had to make it independent and act as the product when the shopper purchases.
So a line item in the OD table can be a quote product or a product product.
Dam again!
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply