Category column in SQL

Question

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

I want to know if thats the most efficient way to structure a category column. Ive seen some databases with numbers to represent the category names. Is that more efficient? — supercoolville
– supercoolville, Commented Mar 7, 2012 at 9:55

Chris · Accepted Answer · 2016-10-26 11:39:14Z

7

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)

edited Oct 26, 2016 at 11:39

Chris

59.7k20 gold badges121 silver badges144 bronze badges

answered Mar 7, 2012 at 9:56

Crsr

6243 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Guillaume Poussel Over a year ago

Please, remove quotes for int values!

Crsr Over a year ago

both. more efficiently because you can run complexe queries (if you need) it's a relationship database. It's better to run queries on int or date fields not on text or varchar fields. Are several reasons..

ccpizza Over a year ago

not the most flexible approach because this way you cannot have multiple categories for a single item; something like stackoverflow.com/a/48324559/191246 would be a more standard approach

Alex Pliutau · Accepted Answer · 2012-03-07 09:55:53Z

0

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)

answered Mar 7, 2012 at 9:55

Alex Pliutau

22k28 gold badges115 silver badges144 bronze badges

Comments

triclosan · Accepted Answer · 2012-03-07 09:58:02Z

0

You can use enum instead of text

answered Mar 7, 2012 at 9:58

triclosan

5,7746 gold badges30 silver badges53 bronze badges

Collectives™ on Stack Overflow

Category column in SQL

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related