Database Design - How do I model a product availability matrix?

I have a list of products with dependencies that determine what a customer
can purchase.
Using a simple example, let's say I have 2 types of widgets, a standard and
a deluxe.
If a customer who has not previously purchased any widget logs on to the web
site, he should see both the standard and deluxe widgets available for sale.
If a customer has previously purchased a standard widget, he should see only
the deluxe widget for sale. If the customer has purchased a deluxe widget,
he should not see any widget products for sale.
Right now this logic is contained in a stored proc but I think it should be
stored in a table that would look something like a matrix.
Has anyone worked with this issue before? If so, how did you handle it?You haven't provided any DDL. I'm guessing but would the following be what
you need:
select
*
from
Widgets w
where
w.Priority >
(
select
max (w2.Priority)
from
Orders o
join
Widgets w2 on w2.WidgetID = o.WidgetID
and o.CustomerID = @.CustomerID
)
I'm assuming that the priority of a deluxe widget is greater than a
standard.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Dave" <dave@.nospam.ru> wrote in message
news:%23GC9eQKZFHA.612@.TK2MSFTNGP12.phx.gbl...
I have a list of products with dependencies that determine what a customer
can purchase.
Using a simple example, let's say I have 2 types of widgets, a standard and
a deluxe.
If a customer who has not previously purchased any widget logs on to the web
site, he should see both the standard and deluxe widgets available for sale.
If a customer has previously purchased a standard widget, he should see only
the deluxe widget for sale. If the customer has purchased a deluxe widget,
he should not see any widget products for sale.
Right now this logic is contained in a stored proc but I think it should be
stored in a table that would look something like a matrix.
Has anyone worked with this issue before? If so, how did you handle it?|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are. Sample data is also a good idea, along with clear
specifications.
You will need a customer history table and an inventory table at least.
Then I woudl build a VIEW that shows what has not been bought. This
is a declarative/relational approach, as opposed to thinking in terms
of procedures and arrays. Very roughly liek this:
SELECT *
FROM Inventory AS I
WHERE sku
NOT IN ( SELECT sku FROM CustomerHistory AS H);|||Thanks Tom.
I should have been more specific in my question. Right now I am still in
the design phase. I was looking for help in modeling a "product
availability" matrix.
Right now this is what I have came up with:
--product list
IF object_id('tempdb..#product') IS NOT NULL
DROP TABLE #product
GO
CREATE TABLE #product
(productid int
,class varchar(24)
,subclass varchar(24)
,priority int)
GO
INSERT #product VALUES (1, 'widget', 'standard', 1)
INSERT #product VALUES (2, 'widget', 'deluxe', 2)
INSERT #product VALUES (3, 'flange', 'standard', 1)
INSERT #product VALUES (4, 'flange', 'deluxe', 2)
IF object_id('tempdb..#customer') IS NOT NULL
DROP TABLE #customer
GO
--customers with active products
CREATE TABLE #customer
(custid int
,productid int)
GO
INSERT #customer VALUES (1, NULL)
--has nothing; products 1,2,3,4 should be available
INSERT #customer VALUES (2, 1)
INSERT #customer VALUES (2, 4)
--has 1 & 4, only product 2 should be available
INSERT #customer VALUES (3, 4)
--has 4, products 1, 2 should be available
Is this a sound design?
Am I going to run into problems if I add a third attribute like say color?
(i.e., widget, standard, blue).
Some time ago I remember reading an article on how to approach this problem
but I can no longer find it.
Any comments or insights are appreciated.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23z4DcuKZFHA.3648@.TK2MSFTNGP14.phx.gbl...
> You haven't provided any DDL. I'm guessing but would the following be
what
> you need:
> select
> *
> from
> Widgets w
> where
> w.Priority >
> (
> select
> max (w2.Priority)
> from
> Orders o
> join
> Widgets w2 on w2.WidgetID = o.WidgetID
> and o.CustomerID = @.CustomerID
> )
> I'm assuming that the priority of a deluxe widget is greater than a
> standard.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> .
> "Dave" <dave@.nospam.ru> wrote in message
> news:%23GC9eQKZFHA.612@.TK2MSFTNGP12.phx.gbl...
> I have a list of products with dependencies that determine what a
customer
> can purchase.
> Using a simple example, let's say I have 2 types of widgets, a standard
and
> a deluxe.
> If a customer who has not previously purchased any widget logs on to the
web
> site, he should see both the standard and deluxe widgets available for
sale.
> If a customer has previously purchased a standard widget, he should see
only
> the deluxe widget for sale. If the customer has purchased a deluxe
widget,
> he should not see any widget products for sale.
> Right now this logic is contained in a stored proc but I think it should
be
> stored in a table that would look something like a matrix.
> Has anyone worked with this issue before? If so, how did you handle it?
>|||This code will give you the numbers you expect:
select
*
from
#product p1
where
p1.priority >
(
select
isnull (max (p2.priority), 0)
from
#product p2
join #customer c on c.productid = p2.productid
where
c.custid = 3
and p2.class = p1.class
)
That said, you should still normalize the product table. You have a
transitive dependency, since priority is related to subclass. That then
gives you 2 tables:
CREATE TABLE #subclass
(subclass varchar(24) primary key
,priority int)
GO
CREATE TABLE #product
(productid int
,class varchar(24)
,subclass varchar(24) references #subclass
GO
Also, be sure to add primary key and foreign key constraints between
#customer and #product.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Dave" <dave@.nospam.ru> wrote in message
news:ug6iO7SZFHA.800@.tk2msftngp13.phx.gbl...
Thanks Tom.
I should have been more specific in my question. Right now I am still in
the design phase. I was looking for help in modeling a "product
availability" matrix.
Right now this is what I have came up with:
--product list
IF object_id('tempdb..#product') IS NOT NULL
DROP TABLE #product
GO
CREATE TABLE #product
(productid int
,class varchar(24)
,subclass varchar(24)
,priority int)
GO
INSERT #product VALUES (1, 'widget', 'standard', 1)
INSERT #product VALUES (2, 'widget', 'deluxe', 2)
INSERT #product VALUES (3, 'flange', 'standard', 1)
INSERT #product VALUES (4, 'flange', 'deluxe', 2)
IF object_id('tempdb..#customer') IS NOT NULL
DROP TABLE #customer
GO
--customers with active products
CREATE TABLE #customer
(custid int
,productid int)
GO
INSERT #customer VALUES (1, NULL)
--has nothing; products 1,2,3,4 should be available
INSERT #customer VALUES (2, 1)
INSERT #customer VALUES (2, 4)
--has 1 & 4, only product 2 should be available
INSERT #customer VALUES (3, 4)
--has 4, products 1, 2 should be available
Is this a sound design?
Am I going to run into problems if I add a third attribute like say color?
(i.e., widget, standard, blue).
Some time ago I remember reading an article on how to approach this problem
but I can no longer find it.
Any comments or insights are appreciated.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23z4DcuKZFHA.3648@.TK2MSFTNGP14.phx.gbl...
> You haven't provided any DDL. I'm guessing but would the following be
what
> you need:
> select
> *
> from
> Widgets w
> where
> w.Priority >
> (
> select
> max (w2.Priority)
> from
> Orders o
> join
> Widgets w2 on w2.WidgetID = o.WidgetID
> and o.CustomerID = @.CustomerID
> )
> I'm assuming that the priority of a deluxe widget is greater than a
> standard.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> .
> "Dave" <dave@.nospam.ru> wrote in message
> news:%23GC9eQKZFHA.612@.TK2MSFTNGP12.phx.gbl...
> I have a list of products with dependencies that determine what a
customer
> can purchase.
> Using a simple example, let's say I have 2 types of widgets, a standard
and
> a deluxe.
> If a customer who has not previously purchased any widget logs on to the
web
> site, he should see both the standard and deluxe widgets available for
sale.
> If a customer has previously purchased a standard widget, he should see
only
> the deluxe widget for sale. If the customer has purchased a deluxe
widget,
> he should not see any widget products for sale.
> Right now this logic is contained in a stored proc but I think it should
be
> stored in a table that would look something like a matrix.
> Has anyone worked with this issue before? If so, how did you handle it?
>