Jump to content
  • Advertisement
Sign in to follow this  
way2lazy2care

Database: int vs char()

This topic is 3036 days old which is more than the 365 day threshold we allow for new replies. Please post a new topic.

If you intended to correct an error in the post then please contact us.

Recommended Posts

So I was in my database class today, and our teacher gave the example of having a char(5) to store a numeric string, and said it would be better to use that than an int because it would save less data. She said that int's were 4 bytes, so it would be 6*4 for a 5 digit numeric instead of 6*2 for 5 digit numeric using char. I asked her, because a 4 byte int should contain all the 5 digit numerals anyway, why char(5) would be better, but she continued to say it was smaller, and told me to count all the bits to get the byte size. I thought that all ints were stored as max 4 bytes standard, so now I am confused. I just don't understand why/if she's wrong or maybe I misunderstood what she was saying completely. Is there a reason to use a char() over an int for a pk in a database?

Share this post


Link to post
Share on other sites
Advertisement
The only way it's taking less space is if you're doing something moronic like storing the digits of a number in separate ints.

And really, most databases have separate sized ints if you're really picky.

Share this post


Link to post
Share on other sites
Does she teach MIS as the school I attended, by chance...? I swear, so much misinformation from my teachers... anyway.

Your int is 4 bytes, as you expected. Char is 1 byte per, so char(5) would be 5 bytes.

Changing things to bits doesn't change anything, since it then becomes 32 and 40 :)

I'm not sure where exactly she'd be getting 6*4 or 6*2...

In any case, if you're capping out at 5 digits anyway, a smallint would technically be your best option at 2 bytes :)

Share this post


Link to post
Share on other sites
Quote:
Original post by Telastyn
The only way it's taking less space is if you're doing something moronic like storing the digits of a number in separate ints.

And really, most databases have separate sized ints if you're really picky.


that's what I thought. We're using mysql, so it would be:

http://dev.mysql.com/doc/refman/4.1/en/numeric-types.html

But thanks for the link, or I wouldn't have looked for the mysql version ;).

Share this post


Link to post
Share on other sites
Quote:
Original post by evolutional
*slaps forehead*

Nothing like seeing teachers spread misinformation, eh?


She's foreign, so I'm giving her benefit of the doubt on me not completely understanding what she meant :-/

Share this post


Link to post
Share on other sites
*pokes eyes*

And even if she would be right, these micro um, 'optimizations' have nothing to do with proper database design and maintenance.

Share this post


Link to post
Share on other sites
How do relational databases typically deal with character-encoding anyway? I'd imagine that you often can't afford to just make everything full 16/32-bit Unicode. and I don't suppose UTF-8 works in fixed-width fields.

Share this post


Link to post
Share on other sites
Quote:
Original post by implicit
How do relational databases typically deal with character-encoding anyway? I'd imagine that you often can't afford to just make everything full 16/32-bit Unicode. and I don't suppose UTF-8 works in fixed-width fields.
It depends on the engine. Most databases allow you to specify encoding on a per-column basis. Some also have specfic UTF-16/UTF-32 datatypes (e.g. NCHAR/NVARCHAR is UTF-16 in SQL Server).

Storing numbers in CHAR fields is silly. Not only does it waste space, but it totally screws up collation as well (for example, "1000" would sort before "2" in text columns).

Share this post


Link to post
Share on other sites
Sign in to follow this  

  • Advertisement
×

Important Information

By using GameDev.net, you agree to our community Guidelines, Terms of Use, and Privacy Policy.

Participate in the game development conversation and more when you create an account on GameDev.net!

Sign me up!