Results 1 to 12 of 12
  1. #1
    Join Date
    Feb 2008
    Posts
    43

    Product searches

    Hi,

    I'm trying to improve the design and performance of this database.

    This is the database for a web-site.
    The search will be mostly done on the product description and there can be up to 2 mil search requests / month

    My first design comments would be to :
    1) remove the Cla_Id field from the List_Prod table
    2) remove the Manager_Cla_Id from the Manager table
    because the List_Id - Cla_Id relationship is already in the List table, and it does not have to be duplicated in other tables. I think this comes from a normal form, but I'm not sure though.

    Can you please help me to understand if my initial comments are good and what can I do more to improve the performance of the searches.

    I thought of indexing, but it sounds quite strange for me to index a character field (Prod_Desc).

    Thanks,
    Ronnyy

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    NOTHING wrong with indexing a character field. Why on earth would you think so? However - how will you be searching it? Will you be searching for exact matches or "contains this phrase"? Percentage of table likely to be returned (range)?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Feb 2008
    Posts
    43
    I was thinking that if you offer such a functionality to search on a product description, which is a VARCHAR2(60) type, then you can't really asume that a user will input an exact match of that Product Description even if you request them, because in the end it's user's input

    Therefore I was thinking how much does it help to build an index on that table, what's the performance benefit of building such an index assuming that there will be both exact matches and partial matches.


    In terms of the data to be retrieved from the search I would say that a join between the List, Clasification and PRoduct table is necessary, and I wonder if the other two tables cannot be included as well, as there are simple joins with the Manager and Persons tables.

    I just realised that the List table has not the best design, it is a table that holds a tree, and maybe a nested set design would be better.

    Ronnyy

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    No point indexing it then.

    Is this Oracle? I can't speak for the internal mechanisms of that. You are really mixing logical and physical design ATM. Probably best to stick to the logical for now and sort out physical in the Oracle forum.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    if you are worried about invalid user inoput then you could consider developing some form of second guseesing algolrhtym.. similar to the soundex or double metaphone. Whether thats worth the effort I don't know. It would be a farily significnat taks, it would require you to break up row into words, or use a key words column

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I'm also surpised you don't have other prod details (name, manufacturer, item code) etc..
    Another alternative (but requires some sort of input - manual or otherwise) is a one to many relationship to a products_tags table containing features & other common search terms. This you could index and provide a drop down for people to search by rather than allowing free text entry.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    I'd leave this problem for the database engine to compensate... Nearly all engines today have some flavor of "full text search" that trades disk for the ability to cope with ill-formed text searches (mis-spelled words, words out of sequence, etc). I can't imagine adapting my schema to do a job that the engine can do better than I can.

    -PatP

  8. #8
    Join Date
    Feb 2008
    Posts
    43
    Thank you for your answers

    Quote Originally Posted by pootle flump
    I'm also surpised you don't have other prod details (name, manufacturer, item code) etc..
    Another alternative (but requires some sort of input - manual or otherwise) is a one to many relationship to a products_tags table containing features & other common search terms. This you could index and provide a drop down for people to search by rather than allowing free text entry.
    I forgot to mention that this is not my design, as I said I would do it with a different type of tree in the List table, so sorry if I can't provide you information on why they did it like this.

    Pat P, making the problem more general, if you have a web-site with 5 mil products lets say(I know that in practice 5 mil may be too much) than you just do a
    SELECT COLUMNS FROM TABLE WHERE SEARCHED_COLUMN LIKE '%SEARCHTERMS%'; ?

    I thought there should be something better than this.

    The Full Text search that you are talking about doesn't it search on all the columns in the database? isn't it better to search only on one column if you know that there is only one column to search on?

    I'm just asking because I don't know it, please understand that I might be wrong.

    Ronnyy

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    We need to see some sample data and some sample search terms.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  10. #10
    Join Date
    Feb 2008
    Posts
    43
    Product Description can be:
    Mountinebike 7 speeds,
    BMX Bike Midle size
    Godzilla T-Shirt
    ...

    List can be T-shirts, Bikes, etc.

    Clasification can be In-door, Out-door, etc...

    Unfortunatelly I don't have access to the database, I made a request and now I have to wait.

    The design that I have to improve is on paper, and now when I'm reading the design for the 30th time, I realize that maybe I miss interpreted it, because on paper the List table for example has the List_ID and List_CLA_ID fields underlined, but the other fields are not underlined like this
    Now I'm thinking that maybe the List_ID and the List_CLA_ID make up the PRIMARY KEY of the List table and this changes a lot the design.

    This means that a List can be classified in more than one classification.

  11. #11
    Join Date
    Feb 2008
    Posts
    43
    Another important aspect is that the web-database is a copy of another master database, so we can assume there will be no inserts and updates on the web database with the exception of the normal daily copy from the master database to the web-database

  12. #12
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Just playing devil's advocate.

    I'm not sure that a one to many list of features\ tags is trying to do a home brew version of full text search.

    1) Can you be\ are you sure that the descriptions are comprehensive with regard to all, or the main, search terms a customer is likely to use to find a specific item?
    2) Would you like customers to have a drop down list of search terms, perhaps in a hierarchy, or is a little google text box sufficient?
    3) Common features allow you to loosely group items. "If you liked this you might like...".

    One to many feature tables are not that rare. For really structured, feature driven stuff it is very common e.g.:
    http://www.laptopsdirect.co.uk/AdvSearch/
    People are also getting more and more used to utilising tags from web 2.0 sites.
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •