Search on multiple criteria

  • Scott Griswold

    Grasshopper

    Points: 13

    All,

    A client has asked for a database that will be able to search on multiple criteria at the same time. I have been unable to come up with a database design that will allow for easy entry and search. I currently have a table that has a record for each search type and value type. I can easily search on one type of criteria. I cannot figure out how to search multiple criteria at the same time.

    Thanks,

    Scott

  • dean gross

    SSCrazy

    Points: 2632

    Check out the dynamic sql articles at http://www.sommarskog.se. he provide a good example of how to do this.

  • Joe Clifford

    SSCrazy Eights

    Points: 8739

    Scott -

    One thing to take a look at in this circumstance is running multiple querys and aggregating the results (e.g. a union), rather than one nasty query that trys to do it all.  Multiple "OR"'s, etc. will just kill your query performance - it's often faster to perform multiple smaller queries than one nasty one.

    Joe

     

  • tdemille-1102706

    SSC Enthusiast

    Points: 135

    Would this work in a scenario where you are searching on up to 40 or 50 criteria? I'm trying to design a super fast search of MLS (real estate, multiple listing) data. Currently we have a table with like 50 rows and we use dynamic SQL. ANy suggestions on how I could do this the way you suggested? Would there be a table for each criteria? IE: A number of bedrooms table, number of baths table, price table, pool view table, etc. etc..

  • dean gross

    SSCrazy

    Points: 2632

    one way is to create a cube in analysis services and have the dimension tables specified with location attributes, lot attributes, building attributes, community attributes etc and then the search would be super fast.

  • tdemille-1102706

    SSC Enthusiast

    Points: 135

    I"m not a BI guy but just did a little research. Are you speaking about creating a star-schema datawarehouse? Would there be a dimensions table for each attribute?

    NumBedroomsDimension

    NumBathsDimension

    LotSizeDimension

    PriceDimension

    PoolDimension

    GolfViewDimension

    MountainViewDimension

    MLSAreaDimension

    etc....

    Everything I find about star schema and data warehouses is about helping organizations manage intelligence, we are looking to implement a fast search triggered by end users on the web, would this still be appropriate?

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply