Re: How to model searchable properties of an entity

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 Sat Aug 14 2004 - 19:08:51 CEST