"Dave" <daven@cougar-automation.co.uk> wrote in message
news:8c6fa0e1.0410270159.1927e065@posting.google.com...
> Hi all,
> i have a table which contains for arguments sake, beans -
>
> Table Beans
> BeanId
> DecriptionId
> DescriptionValue
>
>
> with values such as
>
> Harricot length 1.1
> Kidney length 1.4
> Harricot Colour Pale Orange
> Kidney Colour Red
> Harricot Notes Beans Means Heinz
> Kidney Notes Delicioso con carne
>
>
> How can I write a query to return the best match for say a bean of
> length 1.3 and colour blue maybe relating to Heinz.
>
> The beans are just examples the real data is going to be completely
> varied, the query must be fast because I anticipate having a million
> rows, but what I am most interested in is how to score the results to
> find the best match and was hoping to use SQL to return the best match
> rather then try and score it in the front end application. but i could
> do some there.
>
> I hope this makes sense
>
> Thanks
>
> Dave

"Dave" <daven@cougar-automation.co.uk> wrote in message
news:8c6fa0e1.0410281208.225d635d@posting.google.com...
>
> i dont have a problem with this if you tell me it is the way to go
> regarding creating quick queries, it would however result in a lot of
> rows like -
>
> and then someone might come along with another descriptor -

> someone would provide a set of descriptions and values and the
> database return the closest match to those descriptions, i was
> thinking of each extension of the main app to provide its own piece of
> WHERE clause, but I dont know, maybe just provide the set of values
> and then a number of searches perform different scoring functions on
> each value to provide a set of beans and probabilities? what do you
> think?

"Adam Machanic" <amachanic@hotmail._removetoemail_.com> wrote in message news:<u8gApfVvEHA.2536@TK2MSFTNGP11.phx.gbl>...
> "Dave" <daven@cougar-automation.co.uk> wrote in message
> news:8c6fa0e1.0410281208.225d635d@posting.google.com...
> >
> > i dont have a problem with this if you tell me it is the way to go
> > regarding creating quick queries, it would however result in a lot of
> > rows like -
> >
> > and then someone might come along with another descriptor -
>
> Are these all just arbitrary descriptions? Or are they typed in the
> application? That is, does the UI know the difference between a
> "descriptor" and a "color"?
>
> If the UI can differentiate, then you should definitely store these
> different types in different columns; otherwise, it would be impossible (or
> at least extremely difficult), for instance, to constrain such that a length
> must be numeric whereas a color must be "orange", "red", or "green"... This
> will also make searches much quicker -- if a user wants to search on length,
> you'll be able to take advantage of an index on that column. Not so if you
> have all of the values stored in an arbitrary way.
>
> On the other hand, if the "descriptor" is always just some arbitrary
> thing defined by the user, there's probably not much normalization you can
> do (or that will be necessary to do).
>
> > someone would provide a set of descriptions and values and the
> > database return the closest match to those descriptions, i was
> > thinking of each extension of the main app to provide its own piece of
> > WHERE clause, but I dont know, maybe just provide the set of values
> > and then a number of searches perform different scoring functions on
> > each value to provide a set of beans and probabilities? what do you
> > think?
>
> You could, perhaps, set up tables with associative values for various
> columns; for instance, you might associate the color "orange" with the
> colors "yellow", "red", and "burnt sienna", or you might allow a range on