Mysql: how to structure this data and search it

Question

I'm new to mysql. Right now, I have this kind of structure in mysql database:

| keyID | Param           | Value
| 123   | Location        | Canada
| 123   | Cost            | 34
| 123   | TransportMethod | Boat
 ...
 ...

I have probably like 20 params with unique values for each Key ID. I want to be able to search in mysql given the 20 params with each of the values and figure out which keyID.

Firstly, how should I even restructure mysql database? Should I have 20 param columns + keyID? Secondly, (relates to first question), how would I do the query to find the keyID?

Answer 1

If your params are identical across different keys (or all params are a subset of some set of params that the objects may have), you should structure the database so that each column is a param, and the row corresponds to one KeyID and the values of its params.

|keyID|Location|Cost|TransportMethod|...|...
|123  |Canada  |34  |Boat ...
|124  | ...
 ...

Then to query for the keyID you would use a SELECT, FROM, and WHERE statement, such as,

SELECT keyID
FROM key_table 
WHERE Location='Canada'
AND Cost=34
AND TransportMethod='Boat'
...

for more info see http://www.w3schools.com/php/php_mysql_where.asp

edit: if your params change across different objects (keyIDs) this will require a different approach I think