Click here to Skip to main content
13,093,720 members (60,245 online)
Rate this:
Please Sign up or sign in to vote.
See more:
Hi all,

The question below was deleted for some reason I don't understand (no comment was being made). Perhaps the deleter wasn't familiar with the concept of fuzzy logic. This is okey - just think of the question as relating to ranking in SELECT statements for dealing with big data. So I try again, and have also tried to improve the question to the best of my knowledge. The question itself is very important to me, as it is fundamental to a research problem I am working on. /Petter

Hi all,

I'm working on a "fuzzy logic" way to retrieve and order (ranking/weighing) data from a database. For example, if a person looks for a hotel room in between 100 and 200 euros per night, a room that costs 150 euros might be ranked as number one, a room that costs 199 euros will be ranked rather low in the result list, and a room that costs 202 euros might still be included in the results, in case there are just a few (if any) results that fulfil the user's requirements. Another example: A customer wants to look for red shirts in the 30-50 euro price range, and the database displays darkred shirts as well, as they are a rather close match and cost 40 euros.

I'm now thinking of how to accomplish this behaviour using a SqlServer database. (NOTE: I'm not looking for the built-in freetext search features.)

One way to go ahead would probably mean using Sql functions, to which I send the values that the user enters (for example via a web site form --> stored procedure), so that I end up with a number of functions for various cases of fuzzy logic.

Another way would perhaps be to use Linq to Sql and then write the functions in C# (I might be incorrect here). I'm much better at writing C# code than advanced Sql code plus I can check the code as I test it in Visual Studio, so if this would be feasible, then that would probably be a good thing, but then again, this approach wouldn't be that independent, and I hope to implement a fuzzy logic project here at CodeProject for anyone to use.

A third option would be to do a simple rank in long, nasty-looking stored procedures, using syntax like in[^] However, this approach would not be easy to maintain or to use in other projects.

Perhaps there is a fourth option that I might have missed, but I have done extensive searching and reading on fuzzy logic, and these options are what I have come up with.

I also have yet to see any usable code of these options, so for the moment, I could really use any help that's out there. In other words, if someone is familiar with working with this concept, I would be happy for any pointers.

Thanks very much in advance,

Posted 31-Mar-13 23:02pm
Updated 31-Mar-13 23:03pm
Rate this: bad
Please Sign up or sign in to vote.

Solution 2

Storing, retrieving, filtering, ordering, ranking and aggregation of data is what databases are optimized for and better at. Math and presentation not so.

Your case of fuzzy logics in the filtering is not really affecting it that badly unless you're putting in a lot of math in the queries.
What you definitely should take a look at is Analytic Functions, for example CUME_DIST[^].

The question is, how much can you affect the design of the database?
How you store your data will be of the highest importance, it's really crucial for the query performance.

For example, you shouldn't just store "Red" in a colour column, but rather RGB values, in a column each, and then for example search for CUME_DIST () OVER (ORDER BY ABS(@RED - RED)) to get values close to the red colour you're searching for.

My opinion in short: The math in the fuzzy logics should be done in the application layer, getting/creating the values to calculate from should be done in the database.
petter2012 2-Apr-13 3:41am
Hej Jörgen!

Thanks a lot for the insightful respone and for the pointers.

Petter :)
Rate this: bad
Please Sign up or sign in to vote.

Solution 1

I would keep the fuzzification/defuzzification code on the C# side, because, in my opinion, a powerful programming language like C# is better suited for the task (more elegant and clean code).
My two cents.
petter2012 1-Apr-13 8:41am
I totally agree (SQL is such a hack when one tries to do more than the basic and very neat CRUD operations).

However, I can't figure out how to accomplish this. I mean, sending my code to the C# methods would work just fine, but how could I evaluate the Sql data this way? What I need to do is (I think) to

1. call my stored procedure, including calls to different functions/metods
2. In my where statements, call the functions/methods for each parameter in order to get a weighing number/rating,
3. create a new sql tabl based on the ratings derived from the functions/methods.

This would mean that Sql functions would be the only way to solve this problem, but if it would be possible using C#, then so much better.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month

Advertise | Privacy |
Web02 | 2.8.170813.1 | Last Updated 1 Apr 2013
Copyright © CodeProject, 1999-2017
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100