Re: How to model searchable properties of an entity

CELKO
May be I did not clarify my problem.
My business needs are I need to allow users to define attributes on the fly.
This is not the DBA's fault.

For e.g Part# 1001 has 20 attributes and part # 1002 has 10. User may choose to add 1 more to Part#20. That does not change the part entity. It only adds the attribute. If I do that I will have as many entities as number of parts I have in database.

ABout your point, that data model is screwed is,. well that is my exact question. What should be the data model for this business neeed.

In your own example, If I had 200 cars and each car having one or other attribute different than others, (different meaning, it is specified for one car but not for other) then I will end up with 200 tables.
In my case I have 100,000 parts. I will end up with 100,000 tables.

Dilip

celko212_at_earthlink.net (--CELKO--) wrote in message news:<18c7b3c2.0408131758.65f91710_at_posting.google.com>...
> >> Let us say I have Part# and it has very large number of flexible
> attributes defined by the user, such as color, width, height, etc...
> <<
>
> Fire the DBA immediately; users NEVER get to define the database and
> this bum is not doing his job.
>
> >> Depending on Part number, these attributes may change.<<
>
> No, the values of the attributes will change. If the attributes
> change, we have a new entity, by definition. Or a bad data model.
>
> >> To make the things worse, they are searchable, meaning show me all
> the parts which are blue in color and whose width is less than 10
> inches, etc. <<
>
> If the goods are that weird and changable, look at using a Google or
> EBay document search engine. Put a relational inventory system on the
> front end tpo track the document side of the house.
>
> Very few businesses work this way, just as very people actually search
> on oddball random attributes.
>
> >> If I make, name value pair, them my search will has as many self
> joins
> as I have ands in my query. <<
>
> This design error is called EAV (entity-attribute-value) and it is
> common enough to have a name -- like "cancer" :)
>
> The classic scenario calls for a root class with all the common
> attributes and then specialized sub-classes under it. As an example,
> let's take the class of Vehicles and find an industry standard
> identifier (VIN), and add two mutually exclusive sub-classes, Sport
> utility vehicles and sedans ('SUV', 'SED').
>
> CREATE TABLE Vehicles
> (vin CHAR(17) NOT NULL PRIMARY KEY,
> vehicle_type CHAR(3) NOT NULL
> CHECK(vehicle_type IN ('SUV', 'SED')),
> UNIQUE (vin, vehicle_type),
> ..);
>
> Notice the overlapping candidate keys. I then use a compound candidate
> key (vin, vehicle_type) and a constraint in each sub-class table to
> assure that the vehicle_type is locked and agrees with the Vehicles
> table. Add some DRI actions and you are done:
>
> CREATE TABLE SUV
> (vin CHAR(17) NOT NULL PRIMARY KEY,
> vehicle_type CHAR(3) DEFAULT 'SUV' NOT NULL
> CHECK(vehicle_type = 'SUV'),
> UNIQUE (vin, vehicle_type),
> FOREIGN KEY (vin, vehicle_type)
> REFERENCES Vehicles(vin, vehicle_type)
> ON UPDATE CASCADE
> ON DELETE CASCADE,
> ..);
>
> CREATE TABLE Sedans
> (vin CHAR(17) NOT NULL PRIMARY KEY,
> vehicle_type CHAR(3) DEFAULT 'SED' NOT NULL
> CHECK(vehicle_type = 'SED'),
> UNIQUE (vin, vehicle_type),
> FOREIGN KEY (vin, vehicle_type)
> REFERENCES Vehicles(vin, vehicle_type)
> ON UPDATE CASCADE
> ON DELETE CASCADE,
> ..);
>
> I can continue to build a hierarchy like this. For example, if I had
> a Sedans table that broke down into two-door and four-door sedans, I
> could a schema like this:
>
> CREATE TABLE Sedans
> (vin CHAR(17) NOT NULL PRIMARY KEY,
> vehicle_type CHAR(3) DEFAULT 'SED' NOT NULL
> CHECK(vehicle_type IN ('2DR', '4DR', 'SED')),
> UNIQUE (vin, vehicle_type),
> FOREIGN KEY (vin, vehicle_type)
> REFERENCES Vehicles(vin, vehicle_type)
> ON UPDATE CASCADE
> ON DELETE CASCADE,
> ..);
>
> CREATE TABLE TwoDoor
> (vin CHAR(17) NOT NULL PRIMARY KEY,
> vehicle_type CHAR(3) DEFAULT '2DR' NOT NULL
> CHECK(vehicle_type = '2DR'),
> UNIQUE (vin, vehicle_type),
> FOREIGN KEY (vin, vehicle_type)
> REFERENCES Sedans(vin, vehicle_type)
> ON UPDATE CASCADE
> ON DELETE CASCADE,
> ..);
>
> CREATE TABLE FourDoor
> (vin CHAR(17) NOT NULL PRIMARY KEY,
> vehicle_type CHAR(3) DEFAULT '4DR' NOT NULL
> CHECK(vehicle_type = '4DR'),
> UNIQUE (vin, vehicle_type),
> FOREIGN KEY (vin, vehicle_type)
> REFERENCES Sedans (vin, vehicle_type)
> ON UPDATE CASCADE
> ON DELETE CASCADE,
> ..);
>
> The idea is to build a chain of identifiers and types in a UNIQUE()
> constraint that go up the tree when you use a REFERENCES constraint.
> Obviously, you can do variants of this trick to get different class
> structures.
>
> If an entity doesn't have to be exclusively one subtype, you play with
> the root of the class hierarchy:
>
> CREATE TABLE Vehicles
> (vin CHAR(17) NOT NULL,
> vehicle_type CHAR(3) NOT NULL
> CHECK(vehicle_type IN ('SUV', 'SED')),
> PRIMARY KEY (vin, vehicle_type),
> ..);
>
> Now start hiding all this stuff in VIEWs immediately and add an
> INSTEAD OF trigger to those VIEWs.
Received on Sat Aug 14 2004 - 08:11:11 CEST