supercoolville supercoolville - 1 month ago 16
MySQL Question

Category column in SQL

I was just wondering if this is the most efficient way to use categories:

---------------------------------
| ID | ITEM | CATEGORY |
---------------------------------
| 1 | COOKIES | FOOD |
| 2 | CAKE | FOOD |
| 3 | WATER | DRINK |
| 4 | PEANUTS | FOOD |
| 5 | PEPSI | DRINK |
---------------------------------
↑ int ↑ text ↑ text <- [ type of column ]


Then my query is:

SELECT * FROM `table` WHERE category='FOOD';

Answer

I think if you change a little bit, for example, category column in categories ID,

`---------------------------------
| ID   | ITEM      | CATEGORY   |
---------------------------------
| 1    | COOKIES   | 1          |
| 2    | CAKE      | 1          |
| 3    | WATER     | 2          |
| 4    | PEANUTS   | 1          |
| 5    | PEPSI     | 2          |

Plus a categories table

--------------------
| ID   | ITEM      | 
--------------------
| 1    | FOOD  
| 2    | DRINK     

AND query:

SELECT * FROM `table` WHERE category=1;   (or 2)
Comments