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.