Search code examples
phpmysqlfull-text-searchtagging

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 rows in the database and all search words in different column (1 in each column for each product). In option 1 there will be 1 row with all words in the same column for each product.

Or is there a better way to do this?

Solution

  • Storing your search terms in never-ending additional columns is counter-intuitive to database "normalization". Storing everything in one column is usually the last option since it is much easier to break down search terms if you use multiple columns.

    Make a separate table and join your original table to this table. Your structure would look something like this:

    Original table

    enter image description here

    New table

    enter image description here

    I added a primary key column to your original table. This will make the JOIN easier. Use the following statement to join the two tables:

    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"

    The "search_word" column in the new table are the terms associated with each of your entries in your original table.

    You can add "%" wildcards to your WHERE statement if you'd want fuzzy (return all results that contain your search term) search enabled.