Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 4 of 4
  1. #1
    New to the CF scene
    Join Date
    Nov 2013
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Best way to do search word structure?

    I want to build up a database where there will be about 400 strings. I want to make the database searchable.

    The structure of the database will be like:
    Brand | model |additional products | price | search words | (this is 1 string, there will about 400 strings)

    There will be between 2 and 50 search words on each string. The search are done by clicking a checkbox and the marked checkboxes words will be searched for in the database.


    My question is how is the best way to index all the search words?
    I’m thinking of 2 ways:

    1.
    In the field | search words |all searchable words will be displayed like: 1GB RAM, 512GB RAM, ATA, SATA… and so on for each string. This means that ALL words will be in the same raw on a specific string separated by “,”.

    2.
    Each search word will have its own row like: | search words 1| search words 2| search words 3 | search 4 words 5|….. and so on. In |search words 1| the word 1GB RAM will be. In | search words 2| the word 512GB RAM will be and so on… This means in a string maybe half the search words row will be filled with a search word.

    In option 2 there will be more than 50 raw in the database and all search words in different column (1 in each column for each product). In option 1 there will be 1 raw with all words in the same column for each product.

    Or is there a better way to do this?

  • #2
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,979
    Thanks
    4
    Thanked 2,659 Times in 2,628 Posts
    Never separate a collection with a delimiter. You *can* get away with separate properties, but ONLY if your structure is guaranteed never to change (tip: it's very rare that any guarantee of such will ever be adhered too).
    So, that leaves you with a many to many relation.
    Code:
    +-------------+       +----------------+          +--------------+
    | keyword     |       | itemkeyword    |          | item         |
    +-------------+       +----------------+          +--------------+
    | keyword [pk]|>o---+<| itemid [PK]    |>+------o<| itemid [PK]  |
    | description |       | keyword [PK]   |          | brand        |
    | etc         |       +----------------+          | model        |
    +-------------+                                   | etc          |
                                                      +--------------+
    Now you can use many to many between keywords and items. You can cascade the relations as well, so that when an item is deleted you can eliminate the itemkeyword, and when a keyword is update or deleted you can do the same. It also allows you to search with a simple join:
    Code:
    SELECT i.itemid, i.brand, i.model, k.description
    FROM item i
    INNER JOIN itemkeyword ik ON ik.itemid = i.itemid
    INNER JOIN keyword k ON k.keyword = ik.keyword
    WHERE k.keyword = 'AKEYWORD'
    Of course you don't need to join the keyword table unless you need more information, but I personally suggest that a lookup table is always used (with just keyword you can get away with a many to many using two tables).
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 

  • #3
    New Coder
    Join Date
    Sep 2013
    Posts
    41
    Thanks
    0
    Thanked 1 Time in 1 Post
    Try out with the below example code, it has work fine for me:

    SELECT original_table.*
    FROM original_table AS ABB2
    JOIN new_table AS ABB1 ON ABB1.product_id = ABB2.id
    WHERE search_word = "your search term"

    Hope this helps.

  • #4
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,979
    Thanks
    4
    Thanked 2,659 Times in 2,628 Posts
    Quote Originally Posted by priyankagound View Post
    Try out with the below example code, it has work fine for me:

    SELECT original_table.*
    FROM original_table AS ABB2
    JOIN new_table AS ABB1 ON ABB1.product_id = ABB2.id
    WHERE search_word = "your search term"

    Hope this helps.
    This has no context in relation to the op's question. All this does is find a paired record in a joined table, which yes is what will be done, but has no meaning to the question on what is the proper way to set up the datasets.
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 


  •  

    Tags for this Thread

    Posting Permissions

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