Re: Table design help needed
From: Louis Davidson (dr_dontspamme_sql_at_hotmail.com)
Date: 10/21/04
- Next message: Mike John: "Re: = VS <>"
- Previous message: Bonj: "Re: Multiple databases or not"
- In reply to: Todd Hazer: "Table design help needed"
- Next in thread: Todd Hazer: "Re: Table design help needed"
- Reply: Todd Hazer: "Re: Table design help needed"
- Messages sorted by: [ date ] [ thread ]
Date: Thu, 21 Oct 2004 15:58:41 -0500
I am thinking that your issue here is that you really have two levels of
subtypes.
Product
Other? Container
WaterBottle CoffeeMug
> What I can't figure out how to do is query the matching WaterBottles and
> CoffeeMugs from their entity tables. I think the reason why this is
> difficult is that ProductSet.ProductID should be a foreign key to the
> primary key of the an entity table, it's not because I need to store the
> id's from many entity tables in the ProductSet.ProductID column. Does
> anyone have any idea how this might be accomplished?
You are already doing this, right? The names of the columns are different,
but the primary key of the subtypes are just named differently. Name them
ProductId and it will be easier. Try not to think of the subtyped tables as
"different" tables, but as extensions, so they will rightly have the same
key.
I am thinking what you want to do (if you don't change the structure) is to
have a view, called containerProducts
create view containerProducts
as
select productId, diameter, capType, <product columns>
from waterBottles
union
select productId, diameter, capType, <product columns>
from coffeeMugs
Good indexing of the base tables will be essential to this working fast, but
as long as the number of products is pretty low (not hundreds of thousands,
I would guess) I don't see there being much problem.
So then you could write:
select <columns>
from containerProducts
join caps
where containerProducts.capType = caps.capType
and containerProducts.diameter =
caps.diameter
where ....
Does this make any sense?
-- ---------------------------------------------------------------------------- Louis Davidson - drsql@hotmail.com SQL Server MVP Compass Technology Management - www.compass.net Pro SQL Server 2000 Database Design - http://www.apress.com/book/bookDisplay.html?bID=266 Note: Please reply to the newsgroups only unless you are interested in consulting services. All other replies may be ignored :) "Todd Hazer" <NO_SPAMthazer@gmail.com> wrote in message news:ejhC1d5tEHA.3292@TK2MSFTNGP12.phx.gbl... > I'm trying to figure out how to design my database to store entities in > their own tables and then allow the attributes of these entities to be > compared to each other for querying. > > So, in the example below I have three entities, WaterBottle, CoffeeMug > and a WaterBottleCap. In the three entity tables I have various > different entities of Bottles, CoffeeMugs and Caps with different > Diameters and CapTypes. All of these entities inherit the common > attributes from the Products table which contains the primary key/id for > all products. I now add a couple WaterBottles, CoffeeMugs, and Caps to > the ProductSets table. > > This gives me a ProductSets table that looks something like this. > > ProductSets Table: > > ProductSetID ProductID > -------------------------------------------------------------------------- -- > 1 5 -- This is a WatterBottle > 1 9 -- This is a WatterBottle > 1 8 -- This is a CoffeeMug > 1 4 -- This is a CoffeeMug > 2 5 -- This is a WatterBottle > 2 9 -- This is a WatterBottle > 2 8 -- This is a CoffeeMug > 2 4 -- This is a CoffeeMug > -------------------------------------------------------------------------- -- > > Now what I want to happen is a user chooses a Cap (say CapID 2). Next > the user chooses a one of many predefined ProductSets to use for > choosing their next product So in this case Set 1 is chosen. After the > set is chosen the user then sees all the WaterBottles and CoffeeMugs > that have the same Diameter and CapType as the Cap originally chosen. > > What I can't figure out how to do is query the matching WaterBottles and > CoffeeMugs from their entity tables. I think the reason why this is > difficult is that ProductSet.ProductID should be a foreign key to the > primary key of the an entity table, it's not because I need to store the > id's from many entity tables in the ProductSet.ProductID column. Does > anyone have any idea how this might be accomplished? > > CREATE TABLE Products ( > ProductID int identity(1,1) NOT NULL, -- Primary Key/ID for all products > ManufacturerID int NOT NULL, > Model varchar(50) DEFAULT ('') NOT NULL > -- More attributes that all products use. > ) > GO > > CREATE TABLE WaterBottles ( > WaterBottleID int NOT NULL, -- Foreign key to Products.ProductID > Diameter decimal (4,2) DEFAULT (0) NOT NULL, > CapType char (3) DEFAULT ('') NOT NULL > -- More attributes that are different from other entities. > ) > GO > > CREATE TABLE CoffeeMugs ( > CoffeeMugID int NOT NULL, -- Foreign key to Products.ProductID > Diameter decimal (4,2) DEFAULT (0) NOT NULL, > CapType char (3) DEFAULT ('') NOT NULL > -- More attributes that are different from other entities. > ) > GO > > CREATE TABLE Caps ( > CapID int NOT NULL, -- Foreign key to Products.ProductID > Diameter decimal (4,2) DEFAULT (0) NOT NULL, > CapType char (3) DEFAULT ('') NOT NULL > -- More attributes that are different from other entities. > ) > GO > > CREATE TABLE ProductSets ( > ProductSetID int identity(1,1) NOT NULL, > ProductID int NOT NULL, > ) > GO > > Thanks, > > Todd
- Next message: Mike John: "Re: = VS <>"
- Previous message: Bonj: "Re: Multiple databases or not"
- In reply to: Todd Hazer: "Table design help needed"
- Next in thread: Todd Hazer: "Re: Table design help needed"
- Reply: Todd Hazer: "Re: Table design help needed"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
