trying to work on a new project, i need some ideas to setup this design model.
sql 2000 database and asp.
This is call center environment where agent bid on shift (schedules) base on most disireable to least, and it process base on seniority.well, let's see, you'll need an agent table, a shift table, and a bid table|||Don't forget the junction tables...
What you really should do is interview the users and the data source providers and model the business...
THEN create the logical data model after all the Entites and attributes have been defined....|||Identifying and introducing properties is an on-going process, but of course the earlier the better ;)|||Thanks guys
i will let you guys knows as it goes.
The biggest issue im looking at so far is to automate the process of assigning shitfs to agent base on seniority. Agents with higher seniorites get thier most rank shifts and so on. also there's avaibility of each shift, e.g shitA will only be given to 20 agents only and so on.
seniority is unique number already assigned to an agent. iwant to use store procedures or function to do that manupulation if possible, after all data's has been collected.|||Need to be careful with the "t" and "f" keys. It sounds like the agents queue up at the loo based on seniority, and only the first 20 agents get the best umm ... waste matter !!
Showing posts with label model. Show all posts
Showing posts with label model. Show all posts
Friday, February 24, 2012
Sunday, February 19, 2012
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?
>
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?
>
Subscribe to:
Posts (Atom)