quote:

---

Originally posted by DustinMichaels

I'm not an expert in database design but you may want to use the concept of entity sub types.

To do this make a common table which will share all of the attributes for the different products.

tbProduct
intProductID PK
tbName
...

Then you can use 1 to 1 relationships with the tbProduct table to create your entity sub types.

For example for the concrete component products you could have a table like this.

tbConcreteComponent
intProductID PK FK To the tbProduct table.
...

tbConcreteComponentPart
intConcreteComponentPart PK
intProductID FK to tbConcreteCompoent table.
mnyPrice
...


Then for each other type of entity just extend the idea that I used in the tbConcreteComponent table. However, I'm still a newb at database design so you'll still want to get the opinions of the experts on this site.

Good luck.

Dustin Michaels

---

quote:

---

Originally posted by DustinMichaels

I think LEFT OUTER JOIN could work in this situation, but your
UNION ALL

.......
......

Dustin Michaels

---

USE NORTHWIND
GO

CREATE TABLE tbProduct (
ProductID INT IDENTITY (1,1) NOT NULL,
Product VARCHAR (100) NOT NULL
)

GO

ALTER TABLE tbProduct 
ADD CONSTRAINT tbProduct_pk PRIMARY KEY (ProductID)

GO

CREATE TABLE tbProduct1 (
fkProductID INT NOT NULL,
Var1 FLOAT NOT NULL,
Var2 FLOAT NOT NULL,
)

GO

ALTER TABLE tbProduct1
ADD CONSTRAINT tbProduct1_pk PRIMARY KEY (fkProductID)
GO

ALTER TABLE tbProduct1
ADD CONSTRAINT tbProduct1_fk FOREIGN KEY (fkProductID)
REFERENCES tbProduct (ProductID)
GO

CREATE TABLE tbProduct2 (
fkProductID INT NOT NULL,
Var1 FLOAT NOT NULL,
Var2 FLOAT NOT NULL,
Var3 FLOAT NOT NULL,
)

ALTER TABLE tbProduct2
ADD CONSTRAINT tbProduct2_pk PRIMARY KEY (fkProductID)
GO

ALTER TABLE tbProduct2
ADD CONSTRAINT tbProduct2_fk FOREIGN KEY (fkProductID)
REFERENCES tbProduct (ProductID)
GO

INSERT INTO tbProduct (Product)
SELECT 'First Product'
GO

declare @iProductID int
SELECT @iProductID = @@Identity
INSERT INTO tbProduct1 (fkProductID, Var1, Var2)
SELECT @iProductID, 10, 10
GO

INSERT INTO tbProduct (Product)
SELECT 'Second product'
GO

declare @iProductID int
SELECT @iProductID = @@Identity
INSERT INTO tbProduct2 (fkProductID, Var1, Var2, Var3)
SELECT @iProductID, 10, 10, 10
GO

SELECT p.ProductID, p.Product, One.*, Two.* 
FROM tbProduct AS p LEFT JOIN tbProduct1 AS One
ON p.ProductID = One.fkProductID
LEFT JOIN tbProduct2 AS Two
ON p.ProductID = Two.fkProductID
WHERE p.ProductID = 1

GO

DROP TABLE tbProduct1
GO

DROP TABLE tbProduct2
GO

DROP TABLE tbProduct
GO

quote:

---

Originally posted by DustinMichaels
...................
.......
tbProduct
intProductID PK
tbName
...

---

quote:

---

Originally posted by MikeB

quote:

---

Originally posted by DustinMichaels
...................
.......
tbProduct
intProductID PK
tbName
...

---

Is tbName supposed to be the name of the product table to look for when manipulating data?

I actually thought of doing this for some other things but alot of people seemed to froun upon the idea!

What does everyone think of doing this?

Mike B

---

quote:

---

Originally posted by DustinMichaels

quote:

---

Originally posted by MikeB

quote:

---

Originally posted by DustinMichaels
...................
.......
tbProduct
intProductID PK
tbName
...

---

Is tbName supposed to be the name of the product table to look for when manipulating data?

I actually thought of doing this for some other things but alot of people seemed to froun upon the idea!

What does everyone think of doing this?

Mike B

---

I just messed up when I wrote down tbName. I meant to say charName. The name should be a column in the tbProduct table not another separate table. But I guess it could be possible to have dynamic table's in which the column tbName could point to. I've never done this before though.

Dustin Michaels

---