I really need a debate! Type - Attributes vs Super Type - Sub Types

I have extensively revied both of the design methodologies and I cannot come up with a single clear reason to use one over the other!

Type - Attributes is where you have a table holding the type categories, type, a table holding the type attributes expected and then a table holding the type attribute value:

tbAutombbileCategories
CategoryID | Category
----------
1 | Car
2 | Truck
3 | Motorcycle

tbAutomobileAttributes
AttributeID | fkCategoryID | Attribute
--------------
1 | 1 (car) | Doors
2 | 2 (truck) | Cab
3 | 2 (truck) | Capacity

tbAutomobile
VIN | Category | Make | Model
------------
1 | 1 | Honda | Accord
2 | 2 | Ford | F150

tbAutomobileAttributeValues
fkVIN | fkAttributeID | Value
----------
1 | 1 | 2
2 | 1 | 0
2 | 2 | 1000

Now the above sure is flexible in the sence that a type of automobile can be added without affecting the database schema, but was if some attributes do not take a numeric value? How do you handle computations on attributes specific attributes? Why would I use this structure as opposed to the super type - sub type as shown below?

tbCategories
CategoryID | Category
--------
1 | Cars
2 | Trucks

tbAutomobile (Super Type)
VIN | fkCategoryID | Make | Model
------------
1 | 1 |Honda | Accord

tbCars
fkVIN | Doors |
------
1 | 2

tbTrucks
fkVIN | Cab | Capacity
--------
2 | 0 | 1000

Now, adding new sub types probably isn't very flexible but, now you can specify data types for each attribute instead of using sql_variant, which by the documentation cannot be used in aggregate functions and may render poor result when used with ADO.

Regardless of the method used, alot of back end coding is required for computations, what table to send the attributes, etc...

Can anyone please help me clarify. What method is best and why. So far I am leaning for option 2. More work but seems to be more flexible in the sence of customization of each datatype.

E.G., what if you wanted to specify attributes about the cap that can be supplied to trucks?

tbTrucks
fkVIN | Cab | Capacity | fkCapID
------------
2 | Y | 1000 | 1

tbCaps
CapID | Vendor | Price | et...

Any thoughts at all? I thought this would have been a pretty damn hot topic!

Mike Bhttp://www.databaseanswers.com/normal_forms.htm
http://databases.about.com/cs/specificproducts/g/normalization.htm
http://portal.acm.org/citation.cfm?id=809996&dl=GUIDE&coll=GUIDE
http://databases.about.com/library/weekly/aa091601a.htm

This one is cool

www.bus.tu.ac.th/usr/surat/is304/normal.ppt

This one too...talksing about supertype and subtype

facweb.cs.depaul.edu/yele/Course/IS421/ S6/H10%20ERD%20Advanced%20Concepts.ppt

Used to be Primary Entity and Attributive Entity...whatever|||Originally posted by Brett Kaiser
http://www.databaseanswers.com/normal_forms.htm
http://databases.about.com/cs/specificproducts/g/normalization.htm
http://portal.acm.org/citation.cfm?id=809996&dl=GUIDE&coll=GUIDE
http://databases.about.com/library/weekly/aa091601a.htm

This one is cool

www.bus.tu.ac.th/usr/surat/is304/normal.ppt

This one too...talksing about supertype and subtype

facweb.cs.depaul.edu/yele/Course/IS421/ S6/H10%20ERD%20Advanced%20Concepts.ppt

Used to be Primary Entity and Attributive Entity...whatever
Your last link is invalid.

Anyway, why did you post all the articles on BCNF? Aren't both examples I have illustrated normalized? If they aren't what rules are broken?

I am a newbie, so any explanation would be appreciated!

Also, what method would you use Brett?

Mike B

I need help with the following (SQL Team Cross Post)

I am trying to setup a shape, shape attributes and calculate the cross
sectional area using the formula specified in the tbShapes.Formula field.
See the code below.

What this does is convert the formula

(Width * Flange) + (((Height - Flange) * Leg) * Count)

to

(108 * 4) + (((36 - 4) * 5) *2)

Now I need to calculate the expression above, but the
expression is a varchar string.

Any help?

USE NORTHWIND
GO

SET NOCOUNT ON
CREATE TABLE [dbo].[tbProductCodes] (
[ProductCode] [int] NOT NULL ,
[fkAccountID] [int] NOT NULL ,
[Product] [varchar] (50) NOT NULL ,
[fkShapeID] [int] NOT NULL
) ON [PRIMARY]
GO

INSERT INTO tbProductCodes (ProductCode, fkAccountID, Product, fkShapeID)
SELECT 2001, 1, 'New Product', 1
GO

CREATE TABLE [dbo].[tbProductTemplateAttributeValues] (
[fkTemplateID] [int] NOT NULL ,
[fkAttributeID] [int] NOT NULL ,
[AttributeValue] [float] NOT NULL
) ON [PRIMARY]
GO

INSERT INTO tbProductTemplateAttributeValues (fkTemplateID, fkAttributeID, AttributeValue)
SELECT 1, 1, 108 UNION ALL
SELECT 1, 2, 36 UNION ALL
SELECT 1, 3, 4 UNION ALL
SELECT 1, 4, 5 UNION ALL
SELECT 1, 5, 2
GO

CREATE TABLE [dbo].[tbProductTemplates] (
[TemplateID] [int] NOT NULL ,
[fkProductCode] [int] NOT NULL ,
[Template] [varchar] (50) NOT NULL ,
[fkMixID] [int] NULL
) ON [PRIMARY]
GO

INSERT INTO tbProductTemplates (TemplateID, fkProductCode, Template, fkMixID)
SELECT 1, 2001, 'ProductTemplate', 1
GO

CREATE TABLE [dbo].[tbShapeAttributes] (
[AttributeID] [int] NOT NULL ,
[fkShapeID] [int] NOT NULL ,
[Attribute] [varchar] (50) NOT NULL
) ON [PRIMARY]
GO

INSERT tbShapeAttributes (AttributeID, fkShapeID, Attribute)
SELECT 1, 1, 'Width' UNION ALL
SELECT 2, 1, 'Height' UNION ALL
SELECT 3, 1, 'Flange' UNION ALL
SELECT 4, 1, 'Leg' UNION ALL
SELECT 5, 1, 'Count'
GO

CREATE TABLE [dbo].[tbShapes] (
[ShapeID] [int] NOT NULL ,
[Shape] [varchar] (50) NOT NULL ,
[Formula] [varchar] (100) NULL
) ON [PRIMARY]
GO

INSERT INTO tbShapes (ShapeID, Shape, Formula)
SELECT 1, 'Double T', '(Width * Flange) + (((Height - Flange) * Leg) * Count)'
GO

CREATE PROCEDURE usp_shapes_GetCrossSection

@.iTemplate int,
@.cResult varchar (500) OUTPUT

AS

declare @.cAttribute varchar(50),
@.fAttribute float

-- Get the formula for the templates shape
SELECT @.cResult = s.Formula
FROM tbShapes AS s INNER JOIN tbProductCodes AS pc
ON s.ShapeID = pc.fkShapeID
INNER JOIN tbProductTemplates AS pt
ON pc.ProductCode = pt.fkProductCode
WHERE pt.TemplateID = @.iTemplate

SELECT @.cResult AS Formula

DECLARE AttributeCursor CURSOR FOR
SELECT sa.Attribute,
av.AttributeValue
FROM tbProductTemplateAttributeValues AS av INNER JOIN tbShapeAttributes AS sa
ON av.fkAttributeID = sa.AttributeID
WHERE av.fkTemplateID = @.iTemplate

OPEN AttributeCursor
FETCH NEXT FROM AttributeCursor INTO @.cAttribute, @.fAttribute
while(@.@.FETCH_STATUS = 0)
BEGIN
SELECT @.cResult = REPLACE(@.cResult, @.cAttribute, CAST(@.fAttribute AS VarChar))
FETCH NEXT FROM AttributeCursor INTO @.cAttribute, @.fAttribute
END

SELECT @.cResult AS NewFormula

CLOSE AttributeCursor
DEALLOCATE AttributeCursor
GO

-- Test stored proc

declare @.iTemplate int, @.fResult float

SET @.iTemplate = 1
EXECUTE usp_shapes_GetCrossSection @.iTemplate, @.fResult OUTPUT
SELECT @.fResult AS Result
GO

drop table [dbo].[tbProductCodes]
GO

drop table [dbo].[tbProductTemplateAttributeValues]
GO

drop table [dbo].[tbProductTemplates]
GO

drop table [dbo].[tbShapeAttributes]
GO

drop table [dbo].[tbShapes]
GO

DROP PROCEDURE usp_shapes_GetCrossSection
GO

Mike BMike, I guess I still don't get why your application requires this. Broken down, what you are doing is taking values stored as integers, running them through a procedure that casts them as characters buried in a string, and then looking for a procedure that strips them back out again?

It sound kind of circular.

Without too much difficulty, you could write a store procedure specific to the formula you gave that will parse the values out based on their positional relationship to the parenthesis characters, but it would not be a general solution. It would not work for any other formula.

If you can limit your operations to add, subtract, multiply, and divide, and if you can ensure that your formula will contain plenty of parenthesis to specify operation precedence, then without you might be able to write a recursive function that would be a general solution for simple formulas.|||Originally posted by blindman
Mike, I guess I still don't get why your application requires this. Broken down, what you are doing is taking values stored as integers, running them through a procedure that casts them as characters buried in a string, and then looking for a procedure that strips them back out again?

It sound kind of circular.

Without too much difficulty, you could write a store procedure specific to the formula you gave that will parse the values out based on their positional relationship to the parenthesis characters, but it would not be a general solution. It would not work for any other formula.

If you can limit your operations to add, subtract, multiply, and divide, and if you can ensure that your formula will contain plenty of parenthesis to specify operation precedence, then without you might be able to write a recursive function that would be a general solution for simple formulas.

Thanks for your reply BlindMan, but a couple of people at SQL Team found a solution.

declare @.iTemplate int, @.fResult varchar(500)

SET @.iTemplate = 1
EXECUTE usp_shapes_GetCrossSection @.iTemplate, @.fResult OUTPUT

DECLARE @.stmt nvarchar(4000)
DECLARE @.param nvarchar(4000)
DECLARE @.Eval int

SET @.stmt='SET @.StmResult = ' + @.fResult
SET @.Param='@.StmResult int out'

EXEC sp_executesql @.stmt, @.Param, @.Eval OUT

SELECT @.Eval

Using the dynamic SQL the Equation can be computed. Anyway, if you would like to see the post it is at:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=34179

Mike B|||OK, that was a cool solution. Dynamic SQL.