SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Wizard Mincer's Avatar
    Join Date
    Mar 2001
    Location
    London | UK
    Posts
    1,140
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    How would you approach 'Keywords' ??

    I have some project data that needs to be stored and searched on. Now I have created my full database schema bar one area that I am unsure exactly what the easiest way to approach it is. (this is MySQL).

    I want to store up to 5 keywords about the projects (user input from a form), which can then be used as a seach string later on.

    I am currently thinking either a delimited string field in the projects or a separate keywords table with 2 fields (project_id and keyword).

    I think that moehod 2 would be better practice, and I'm unsure how exactly I'd search on the delimited string field. But I'd like a couple of opinions, and any other suggested methods.

  2. #2
    I have an opinion...
    Join Date
    Sep 2001
    Location
    Barrie, Ontario
    Posts
    324
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    If you do delimited list, you could do something like this:

    Code:
    SELECT * FROM tblProject WHERE proKeyword LIKE '%strQuery,%';
    It would be slighly more accurate than just:
    Code:
    SELECT * FROM tblProject WHERE proKeyword LIKE '%strQuery%';
    Egotist: A person more interested in himself than in me.
    KodeKrash - Eidix - Barrie LUG

  3. #3
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I would place them in a keywords file. Recall that a LIKE '%anything' will result in a full table scan because the optimizer cannot use an index to match rows, so once you get many, many rows in the table it will bog down heavily. With a keywords table you can do something like this - LIKE 'bob%' and use an index on the keyword col.


Bookmarks

Posting Permissions

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