Modeling As Data Or Metadata?

From: drop the numbers <123tiseo.paul_at_123mayo.edu>
Date: Mon, 25 Mar 2002 22:42:48 GMT
Message-ID: <MPG.17097272119df789896c2@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 Mon Mar 25 2002 - 16:42:48 CST