Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: C# SQL logic
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 http://stackoverflow.com/questions/9329678/sql-search-query-how-to-assign-weight-to-each-input-parameters-and-how-to-order[^] 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,
 
Petter
Posted 1-Apr-13 0:02am
Edited 1-Apr-13 0:03am
v2
Rate this: bad
good
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.
  Permalink  
Comments
petter2012 at 2-Apr-13 3:41am
   
Hej Jörgen!
 
Thanks a lot for the insightful respone and for the pointers.
 
Petter :)
Rate this: bad
good
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.
  Permalink  
Comments
petter2012 at 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
0 OriginalGriff 7,903
1 Sergey Alexandrovich Kryukov 7,192
2 DamithSL 5,604
3 Manas Bhardwaj 4,986
4 Maciej Los 4,820


Advertise | Privacy | Mobile
Web01 | 2.8.1411023.1 | Last Updated 1 Apr 2013
Copyright © CodeProject, 1999-2014
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