Database Design Question

Question 1:

I have a contacts table which holds different types of contacts. I contact can only have 1 type. I have a contact types table and store the id of the contact type in the contacts table. I also have another table (table 1 for example) that has a 1 to many relationship with a contact. A contact can have 1 or many of table 1. But only a certain type of contact. Not all contact types have this relationship with table 1. They aren't related to table 1 at all. Is this ok, and how is it represented in a database diagram?

Question 2:

I have a contacts table and an address table and a contact can have 1 or many addresses. I also have a vendors table. They will also have addresses. I want to share the address table with contacts and vendors. Would I just have an address id as a primary key. Use the contact id or vendor id (depending on what type the address is for) as a foreign key, and then also have another column in the address table that held lets say a 0 or 1 depending on whether or not it was a contact address or a vendor address? Would this be the proper way to do this?

Any suggestions are welcome and thank you in advance!Question 1:
Is this ok, and how is it represented in a database diagram?most definitely yes, it is okay

diagram? diagram?

(you have to imagine jim mora saying "playoffs? (http://youtube.com/watch?v=5rmtO9_wzlI)")

is this a homework assignment?

Question 2:
Would I just have an address id as a primary key.yes

the other part is trickier

you could, for example, have both a contact id and a vendor id foreign key in the address, both of which must be nullable, since one of them (the one it isn't an address for ;)) will be null on every row

i personally would not have a 0 or 1 flag for whether or not it was a contact address or a vendor address|||lol...no..not a homework assignment. Just trying to be very organized on this project! Thanks for your reply. Would you recommend sharing the address table, or, would you have a separate address table for contact and vendor? I would think it makes sense to have only 1 address table.|||I would think it makes sense to have only 1 address table.okay, let's go with that for a second

why?|||why what...have a diagram, or have 1 address table?|||why does it "make sense" to have only one address table? how is it better than two?|||less duplication? Can I run another design by you? I'm really not doing homework...it's been a long time since I've done any database design so I'm rusty.

I have this situation.

A person can hold a season ticket for 1 or more teams. A team has more than 1 game and a game has more than 1 ticket. Here is how I'm designing my DB (not, only including necessary fields right now) Does it make sense to you?

Table:
tblTeam
Field(s):
TeamID PK

Table:
tblContacts
Field(s):
ContactID PK
(note: I will be using the contacts table for people other than season ticket holders as well)

Table:
tblSeasonTicket
Fields(s):
SeasonTicketID PK
TeamID FK
ContactID FK

Table:
tblGames
Field(s):
GameID PK

Table:
tblTickets
Field(s):
TicketID PK
SeasonTicketID FK
GameID FK

Appreciate your thoughts if you're willing to give them.|||less duplication? i don't think so

how often will a vendor and a contact share an address?|||sorry...what I meant was less tables...I'm going to have the same fields for an address. instead of creating a new table, I would only have to add a field. I actually prefer your way with the two tables...I find that easier to keep track of.|||you can bill me for it if you want|||no, my answers on public discussion forums are always free :)|||great...do you have a comment about my ticket scenario? :-)|||is it possible to get a ticket for a game that isn't a season ticket?|||no, it is not possible to get a ticket for a game that isn't a season ticket

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