Re: Modeling As Data Or Metadata?

What if you made categories of features, and each feature was constrained by the category. For example,

Table Category:
Category ID (PK)
Name

Table Features
Feature ID (PK)
Category ID (PK)
Name

If you had a category for System it could hold features like Category Table:
Category ID: 1
Name: CPU

Feature Table:
Feature ID: 1
Category ID: 1
Name: CPU Make

Feature ID: 2
Category ID: 1
Name: Model

Feature ID: 3
Category ID: 1
Name: Speed

You could then make a constraint saying every product of type of "Computer" Needs features 1,2,3. I don't know if this helps. You could run into many problems. You best bet would be to figure out the main features you want to report on, ID, Categorize them and then constrain the absolutely essential. You could always easily add more categories and features.

Adam

"Paul Tiseo" <123tiseo.paul_at_123mayo.edu (drop the numbers)> wrote in message news:MPG.17097272119df789896c2_at_news.easynews.com...
>
> Hi,
>
> A post by no_spam_for_me on 2/27 and a reply by Celko ("A
> searchable datastructure for represeting attributes?") has made me think
> that I might be approaching a certain model in an incorrect way. How
> would one model, for example, a catalog of computer parts and their spec
> sheet features, or maybe a set of biological specimens and their
> possibly varied results?
>
> For the former, would I be making the "OO Programmer's Mistake" if
> I modeled (extraeneous attributes deleted) as follows?
>
> CREATE TABLE Feature(
> FeatureID int IDENTITY(1,1),
> Name varchar(30) NOT NULL,
> CONSTRAINT PK3 PRIMARY KEY NONCLUSTERED (FeatureID)
> )
>
> CREATE TABLE Product(
> ProductID int IDENTITY(1,1),
> ManufacturerNb varchar(30) NULL,
> CONSTRAINT PK1 PRIMARY KEY NONCLUSTERED (ProductID)
> )
>
> CREATE TABLE ProductFeature(
> ProductID int NOT NULL,
> FeatureID int NOT NULL,
> Value char(10) NULL,
> CONSTRAINT PK2 PRIMARY KEY NONCLUSTERED (ProductID, FeatureID),
> CONSTRAINT RefProduct1 FOREIGN KEY (ProductID)
> REFERENCES Product(ProductID),
> CONSTRAINT RefFeature2 FOREIGN KEY (FeatureID)
> REFERENCES Feature(FeatureID)
> )
>
> As you can see, ProductFeature("Value") potentially suffers from
> the same malaise that Celko describes in his reply. It has to be a
> generic varchar. I have no way of stating constraints on the value of
> ProductFeature("Value") in the DBMS except through complicated code that
> depends on what Feature it is or making sure that higher level code
> always puts data in properly.
>
> Does one need to go to a "table per feature" approach, or is there
> a more general way that can anticipate new feature types or new test
> results?
>
> Thanks.
>
> (Any opinions expressed are strictly mine only and not my employer's)
> --------------------------------------------------------------------
> Paul Tiseo, Intermediate Systems Programmer
> Reply To: 123tiseo.paul_at_123mayo.edu (drop the numbers)
Received on Fri Mar 29 2002 - 15:39:20 CET