Re: How to model searchable properties of an entity

>> 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. <<

But it is his responsibility. You do not let users define attributes on the fly. NEVER.

If you do, you will soon have "color" from an American, "colour" from a Brit, "clr" from a geek who likes to abbreviate, and "clor" from someone with bad typing skills. The values will "red" from the English speakers, "rouge" from the Francophones and the Pantone color number from the people who really use colors (http://www.pantone.com).  Likewise, numeric values will be given on different scales and to different precision.

Data integrity lasts about a year in actual use with this kind of design.

>> That does not change the part entity. It only adds the attribute.
<<

What is the BASIC definition of an entity in the RDBMS? An entity *IS* defined by a single set of attributes and it is modeled in a table with columns that have those attributes.

The whole point is that this no longer the original Parts table when it changes. If I started with a wooden charriot, added a motor, rubber tires, a metal body, etc. would it still be that same charriot?  Nope, I converted it into an automobile.

You are thinking like an OO person in a relational database.

>> About your point, that data model is screwed, well that is my exact
question. What should be the data model for this business need. <<

I gave you one way to do class heirarchies with PK-FK references. The overhead is pretty bad if the nesting gets deep or very wide. But you have to actually design each final sub-class.

>> 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. <<

Yes, this is why we have a limited range of types of vehicles and options, which are defined by law for tax purposes, marketing, etc. The customizations are off to the side in 200 small tables, if you really needed huge amounts of data on them. Any answer is going to be VERY industry-specific.

The ultimate "high volume, high turnover rate, mixed inventory" problem is EBay. They use text for their descriptions and do document management, not RDBMS. They do let the users write their own data, but use parsing routines to put the item in a category and extract key words for text searching. Received on Sat Aug 14 2004 - 21:02:32 CEST