CODEWITHSUNDEEP
mysqlsql
supercoolville
supercoolville

Reputation: 9086

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';

Upvotes: 4

Views: 6970

Answers (3)

Crsr
Crsr

Reputation: 624

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)

Upvotes: 7

triclosan
triclosan

Reputation: 5714

You can use enum instead of text

Upvotes: 0

Alex Pliutau
Alex Pliutau

Reputation: 21957

Do you know about normalization forms? You have duplicate data in your table. It more beautiful to create 2 tables: item, category.

item: (id, item, category_id)
category: (id, name)

Upvotes: 0

Related Questions