#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2001
    Location
    Leeds, UK
    Posts
    171
    Rep Power
    15

    normalised design causing problems displaying results


    Hi

    I s'pose this is more of a database design query.

    I have a database holding information about properties, along with what features these houses have, eg. number of bedrooms, type of property (eg. detatched, terraced), garden, etc. My client would like to be able to add more features in the future through the admin section without having to ask us o modify the web site.

    If these features weren't going to change, i'd have a column for each of these features in the main property table. I've had to fix some databases where the programmer allowed the user to add/delete columns before, so I think that's a bad route to go down. So i'm reckoning on a table with questions, eg. number of bedrooms, an answer table, with all of the answers for each question, and an intermediate table between the answers table and the main property table, which holds the which answers apply for the property.

    The only trouble with this method is that to do a property search, the results have several records for each property due to joins and one-to-many relationships, which makes paging on the asp page very difficult and probably very inefficient. Using ado paging for this just will not work, because the number of results returned bears no relation to how many actual properties there are.

    Has anyone gt any suggestions? I've been trawling the net for answers with no success. I can't believe no-one's had this problem before!

    Many thanks

    EmilyB
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,395
    Rep Power
    4286

    Re: normalised design causing problems displaying results


    yes, set up a table for the "attributes" like bedrooms, driveways, does it have a roof or not, etc.

    if you do it right, those will be values in a column, totally generic, the user/administrator will simply be adding data when it is necessary to incorporate a new attribute into the structure of a property

    perhaps this thread on, um, another forum may be of assistance
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2001
    Location
    Leeds, UK
    Posts
    171
    Rep Power
    15
    yes, i've set it up like that, the problem is paging through results and displaying X number of results were found.

    i tried clicking your link, but it just redirected me to the main forum index and i don't seem to be able to find the topic.

    Deadline for the whole web site is thursday so i'm starting to panic now

    Thanks

    Emily
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2001
    Location
    Leeds, UK
    Posts
    171
    Rep Power
    15
    right i found the forum topic.

    yikes.

    it mentions limiting the results, but doesn't really touch on how to solve the problem of multiple rows being returned for each property.

    Deadline's creeping closer and i'm disgusted with myself that i'm gonna have to fudge this one. I was also suprised how difficult it was to find out about this method of setting up a DB and then actually using the data.

    Thanks anyway

    Emily
  8. #5
  9. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,395
    Rep Power
    4286
    there is no "problem" of multiple rows for each property -- that's the way it's designed

    if you want to page, then yes, i agree, the multiple rows make the queries trickier

    this thread (on this site) shows how to create "prev" and "next" links

    you are using micrososft sql server, so subqueries are allowed

    here's a sample of data that might be returned:
    Code:
    property    | attribute   | value
    123 Pine St | bathrooms   | 2
    123 Pine St | pricerange  | high
    123 Pine St | takeback2nd | yes
    5 Elm Lane  | bathrooms   | 1
    5 Elm Lane  | bedrooms    | 1
    15 Oak Terr | bedrooms    | 7
    15 Oak Terr | garage      | 2
    15 Oak Terr | heating     | oil
    these results would come from this type of query:
    Code:
    select columns 
      from properties
    inner
      join attributes
        on ...
     where property in
           ( select property
               from properties
             inner
               join attributes 
                 on ...
              where attribute = X1 and value = Y1     
                 or attribute = X2 and value = Y2
                 or attribute = X3 and value = Y3
                 ...
             group
                 by property    
             having count(*) = N
           )
    notice that the subquery does the filtering of properties based on chosen attribute values, while the outer query returns all attribute values for each property

    in order to do paging, you'll want to use top 10 on the subquery, and use another subquery within that with where propertyid > currid

    helps?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2001
    Location
    Leeds, UK
    Posts
    171
    Rep Power
    15
    hmmm...i had 4 tables involved: property_tbl, option_tbl, property_option_tbl, features_tbl.

    Each record in the feature_tbl has several rows in the option_tbl...this is so i can use these as a lookup for dropdowns in forms. Then for each feature fo the property there would be an option_id and a property_id in the property_option_tbl.

    And then I joined all these with inner joins, to get exactly what you get with your results.

    How can u work out how many pages of properties there are if you don't know how many properties were returned? I've managed this before, but only because i was just doing simple next/prev paging. I know how to do next/prev usign the id...my problem is that the client wants:

    X records found, page 1|2|3|5, showing page 3.

    And yes normally i know how to do that also, but the duplicate records for each property is what has me stumped, as the number of records returned bears no relation to the number of properties returned. And without running the entire query again but selecting only DISTINCT property_id, or even cycling through the results keeping a count of property_id's before I display them later in the page I don't know what else to do. I haven't gone down either of those routes cos I don't like them.
    Last edited by emilyb; January 19th, 2004 at 10:00 AM.
  12. #7
  13. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,395
    Rep Power
    4286
    there is no way to know how many properties satisfy the selection criteria (number of bathrooms > 3, etc.) unless you run the query that finds all such properties

    but you would only have to do this once...

    ... which you can actually do quite easily, the first time the query is run

    in other words, when a new set of selection criteria is entered, run the entire query without the TOP restriction, so that you can use RecordCount (or whatever the function is called in your scripting language) to tell you how many records were returned, even though you show only the first 10 or whatever

    then when you call "next" you can pass the number
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  14. #8
  15. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,395
    Rep Power
    4286
    oh, wait, i just realized you were using

    1 | 2 | 3 | ...

    and presumably these would each be a link to Page 1, Page 2, etc.?

    a bit trickier, that
  16. #9
  17. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2001
    Location
    Leeds, UK
    Posts
    171
    Rep Power
    15
    erm yes indeed!

    i'm stumped
  18. #10
  19. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,395
    Rep Power
    4286
    change the user's expectations
  20. #11
  21. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2001
    Location
    Leeds, UK
    Posts
    171
    Rep Power
    15
    yeah i nearly put that on my last post!

    answer: convince the client they don't want it!

    Doh! I think i might go and get and job stacking supermarket shelves instead
  22. #12
  23. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,395
    Rep Power
    4286
    Originally posted by emilyb
    answer: convince the client they don't want it!
    no, show the client the paging links that you feel comfortable implementing

    heck, even google does "Results 1-10 of approx 26,000"

    easy links at the bottom would be First, Prev, Next, and Last

    anything else is too tough

    now, if the user balks, tell the user about nested subqueries, and preprocessing the entire result set every time, and the performance penalty this implies for the site visitors (and watch their eyes glaze over...)

    or do it the smart way: something along the lines of "sure, i can do it the way you want, no problem, but it requires a lot of extra programming, i'd estimate at least an extra ten hours or so, and as you know, i bill $100/hour, so it's up to you, cher client..."
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  24. #13
  25. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2004
    Posts
    49
    Rep Power
    12
    Why bring back a row for each piece of criteria queried? If the user entered the search criteria they know what the values will be. Just bring back the Propterty (House) and simply page through the results using one of the hundred ways of paging in ASP.

    Clicking the property could drill into the detail which would show all the values for the custom attributes.

    Just a thought.

    Adam
  26. #14
  27. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,395
    Rep Power
    4286
    adam, in general, you would be right, but then again, maybe not

    if i ran a search for a property based on these criteria:
    1) more than 3 bedrooms
    2) vendor will take back a 2nd mortgage

    and all i got back was a list of houses with their street addresses, and i had to click on each one to find out anything else about them, then i'd be peeved, wouldn't i

    but then, that's just me
  28. #15
  29. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2001
    Location
    Leeds, UK
    Posts
    171
    Rep Power
    15
    yep so would i....users need at least a brief description of the property in order to even know which ones they want to click on to find out more....

    there are loads of national property web sites in the uk....but i have a sneaky suspicion they don' bother having such a system as we've been discussing....and perhaps what i'm doing is over-elaborate for what it is, maybe. I think some of the big property sites just use a free-text field for all the features of a property other than price, number of bedrooms, location and type of property (eg detached, bungalow - do u even have bungalows in canada and US? ), but that way users can't do an advanced search based on more in-depth criteria, which i found really annoying when i was hunting for my house.

    And besides, I really feel like i'm not doing myself justice if i turn round to my boss/client and say "no you cacn't have that it's too hard". I'd never learn anything new. And, probably a downfall, I'm a perfectionist but that's not always a good thing!

    My solution though...i fudged it the deadline was short! it's a combination of my first database design (mainly because each available option for each feature has to have a % weighting in order to calculate an estimate of the value of the property - a wacky idea since a lot of that has human elements involved in it but hey the client wanted it!) and some cheating to make coding easier. If i can find a way of doing it in the future i might come back to it, if only for my own sanity!

    Thanks for all your help and suggestions - i find the db design bit the easiest, it's the formatting the results how the client wants it that's so hard!

IMN logo majestic logo threadwatch logo seochat tools logo