Page 1 of 1

Best way to do search word structure?

Posted: Wed Nov 06, 2013 7:54 am
by GertK
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?

Re: Best way to do search word structure?

Posted: Wed Nov 06, 2013 1:00 pm
by requinix
The better way is what you said for #2 about having a row for each word, but the difference being you actually have a row for each word and not a column like you showed.

It's called normalization, and the two basic rules (there are many) are
1. Don't put multiple pieces of data into one table cell (no "1GB RAM, 512GB RAM, ATA, SATA")
2. Don't create many columns for many instances of the same type of data (no "1GB RAM | 512GB RAM | ATA | SATA")

Instead make a brand new table that has two columns, potentially more depending: (1) the ID of whatever brand/model/product the keywords are for, and (2) one keyword. Then make multiple rows for multiple keywords.

Code: Select all

id | keyword
---+--------
 1 | 1GB RAM
 1 | 512GB RAM
 1 | ATA
 1 | SATA