CODEWITHSUNDEEP
phpmysql
Incognito
Incognito

Reputation: 455

Integer vs String field mysql design approach for php web

I have a table with products that contains 2 fields.

1)id of the product int-7digits

2)name of the product

The name of the product can only be 27 different values like banana,apple etc

Now im wondering what is the best approach for my web-server resources. I mean i should use 1,2,3,... values or string values?

~Edit my table will be about 1million rows and steadily increasing over time.

Upvotes: 0

Views: 186

Answers (2)

Branko Dimitrijevic
Branko Dimitrijevic

Reputation: 52107

Use VARCHAR.

Alternatively, if you are absolutely certain the products will stay static, you could use ENUM to save some space, but there won't be much space to save on only 27 rows.

However, if you need to reference the products from other tables, consider creating a surrogate key (auto-incrementing integer), to save space in the referencing tables (compared to repeating VARCHAR product names or 7-digit product IDs).

NOTE: To "save space" is not just about storage space - smaller data means less I/O and more effective caching with the given amount of RAM. This is not important for small tables, but can make a difference on large ones (e.g. if you have a big "order items" table that references products).

Upvotes: 1

Glitch Desire
Glitch Desire

Reputation: 15023

You should use VARCHAR for the name field, for readability. If you have to come back to this in 9 months and remember if 1 is an apple or a banana, you're going to struggle a lot.

As I assume you have to print out product names, you're basically choosing between selecting strings in PHP or MySQL. Either SELECT * FROM fruit WHERE product_name = 'Banana' or SELECT * FROM fruit where product = 7 and then foreach($fruits as $fruit): if $fruit['id'] = $row['product'] { return $fruit['name']; } endforeach;

Assigning values from a PHP array array(1 => 'apple', 2 => 'banana' ... 27 => 'eggplant'); is going to use more overhead than a MySQL SELECT statement on a VARCHAR field, as well. Any performance you gain on the database side will be lost fivefold on the application side.

Upvotes: 1

Related Questions