Re: How to model searchable properties of an entity

I still don't know how to convince you guys. You are all pointing to the process issues that users may give different spellings for color.
I can handle that part by having an admin to create the properties who makes sure that the property being added to the part is really required and is not used in past by any one in any different context.

But the basic fact does not change.
I need to keep adding these properties from time to time because user communitiy can not decide all of them upfront.

Also, I may have 500 such properties and I can have up to 1M different part numnbers. This will give you some idea of complexity. User may choose, show me all the parts with width 10 inches, and price less than $10 and .... can go on for ever. I need to come back in resonable time (Reasonable can be couple of seconds)

Also note that the Search engine techniques used by EBya like company works only if these attributes are no updated. If they are ever updated, Search engines like inverted index fails miserably.

So, please try to understand my problme and accept it as it is and if you have solution to solve it, please let me know. Dilip

toddkennethbenson_at_yahoo.com (Todd B) wrote in message news:<ef8e4d1e.0408140908.6cef8c9f_at_posting.google.com>...
> dilip_angal_at_yahoo.com (Dilip Angal) wrote in message news:<df683587.0408122214.51852da3_at_posting.google.com>...
> > Hi
> > I have been struggeling with this question for a while.
> >
> > 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...
> > Depending on Part number, these attributes may change.
> >
> > 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.
> >
> > Now how do I model my parts table.
> > If I make, name value pair, them my search will has as many self joins
> > as I have ands in my query.
> > If I model attributes as columns, then I don't even know all the
> > possible attributes I can have and besides, even if I know them, not
> > all attributes are applicable to all the parts and hence I have really
> > screwed up table
> >
> > Any help will be appreciated.
> > Dilip
>
> create table inventory (
> part_number int not null primary key;
> part_name varchar(100)
> );
>
> create table attributes (
> attribute_name varchar(20) not null primary key
> );
>
> create table attributed (
> attribute_name varchar(20) not null,
> part_number int not null,
> foreign key attribute_name references attributes (attribute_name) on
> update cascade on delete cascade,
> foreign key part_number referenes inventory (part_number) on update
> cascade on delete cascade,
> primary key (attribute_name, part_number)
> );
>
> Of course, this only works if all of your attributes are of the type
> varchar(20) and only allow an attribute on a single part once (i.e.
> you can't be both 'blue' and in addition 'blue').
>
> Also, if you have a hierarchical model (like in a bill of materials),
> you might be better off looking into nested sets or nested intervals.
> In that case, see some of Joe Celko's or Vadim Tropashko's work. It
> would require, however, some procedural code, if you are not
> disinclined to that.
>
> Todd
Received on Mon Aug 16 2004 - 04:04:07 CEST