Solved

Boolean variable in mysql database

Posted on 2012-04-06
14
574 Views
Last Modified: 2012-04-08
hello experts,
i created a game in php, i want to give each kind of unit in the game some attribute..
for example:
tank is ground unit and he can attack only ground units.
helicopter is air unit and he can attack ground and air units.

so i create the database witch contains all the information about the units.
my question is witch variable i should use to save that unique attribute.(tinyint, bit, varchar, enum)

my idea was to take bit variable for each attribute(attack_air: 0/1, attack_ground: 0/1) because bit take the smallest part in the memory. varchar is way to much. tinyint might be the solution.
but i want to create it in the very best way that possible.

thanks ahead :)
0
Comment
Question by:Puzatiy
  • 7
  • 7
14 Comments
 
LVL 24

Expert Comment

by:johanntagle
Comment Utility
Yes tinyint should do.  As a matter of fact, in frameworks such as Ruby on Rails, specifying a boolean creates a tinyint when using a mysql database.
0
 
LVL 3

Author Comment

by:Puzatiy
Comment Utility
ok thanks alot :)
do you think that bit is ok as well?
i simply want a true or false value, thats all, i assum that bit is basically the variable that im looking for.

but for the attuide i think that tiny int is better.. right?

by the way, do you think that a 2 bit is better that tiny int.
because i tell you what i just thougth.

if he is ground unit then he get 1 in the value else 2
now the attack goes like this 2 bit with can be (01/ 10/ 11) so 11 is 3 that's meen 1 + 2 witch is ground and air, 1 is only ground and 2 is only air, i think i'll stick to this one..
but, i would i need an expert opinion..

thanks again:)
0
 
LVL 24

Accepted Solution

by:
johanntagle earned 500 total points
Comment Utility
If you're using myisam tables, multiple bit columns get combined and are stored more efficiently (e.g. 10 bit columns will not result in 10 bytes).  But on innodb each bit column gets stored as 1 byte, same as tinyint.  But of course, innodb has other advantages such as less corruptibility and support for foreign keys.

Btw, see this interesting article on different options, such as using char(0) http://www.mysqlperformanceblog.com/2008/04/23/efficient-boolean-value-storage-for-innodb-tables/
0
 
LVL 3

Author Comment

by:Puzatiy
Comment Utility
im using mysql database.
and can you help me understand why there is a bit variable.
he take more memory space then tiny int (in the case that they are not the same) and tinyint is way easier to read and write..

thanks again, helped a lot :)
0
 
LVL 24

Expert Comment

by:johanntagle
Comment Utility
FYI myisam and innodb are different storage engines in mysql.  See http://dev.mysql.com/doc/refman/5.1/en/storage-engines.html.  In myisam, multiple bit columns are combined to use less storage space especially when each one needs less than one byte (e.g. b'101' covers only 3 bits).  But in innodb each column still takes the whole byte.
0
 
LVL 3

Author Comment

by:Puzatiy
Comment Utility
but what if i have 2 bits in the same coulmn, is it take the same as tow column with with 1 bit?
or it doesn't metter bit(1), bit(1) = bit(2)


thanks alot!!!! :)
0
 
LVL 24

Expert Comment

by:johanntagle
Comment Utility
Again, myisam or innodb?  In innodb the space usage for 2 bit(1) columns will be two bytes and just 1 byte for 1 bit(2) column while on myisam it will be one byte for both cases.  And I would think mysql has to do more to handle 2 bit(1) columns vs 1 bit(2) column,  regardless of engine.  However, you have to weigh this against what your app needs to do - with a bit(2) column your app still has to parse the two values vs already having them once read from the database in the case of 2 bit(1) columns.  You have to test for yourself and see which performs better.
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 3

Author Comment

by:Puzatiy
Comment Utility
ok thanks ALOT!!! :) and i use myisam.
just so i know for sure, tinyint is basically taking the same memory amount as 2 bit's in myisam engin right?
0
 
LVL 24

Expert Comment

by:johanntagle
Comment Utility
Yes.  Please also see http://dev.mysql.com/doc/refman/5.1/en/storage-requirements.html for all your datatype storage information needs =)
0
 
LVL 3

Author Comment

by:Puzatiy
Comment Utility
thanks alot :)
can i have your email or anything that i can stay in touch with you?..
 i think i might nned more help on the subject.. :)

thanks for everything.. :)
0
 
LVL 24

Expert Comment

by:johanntagle
Comment Utility
I check E-E almost every day, even multiple times a day, so Just post your question here.  there are many other experts who can also help you.  Sorry but direct consulting with me is not free :-)
0
 
LVL 3

Author Comment

by:Puzatiy
Comment Utility
hhh, no i'll simply want your mail so when i'll ask a question i'll send you a mail with the link.
but if you say that you visit in e-e multiple time in a day, then i'll be fine.. :)

thanks again.
0
 
LVL 3

Author Comment

by:Puzatiy
Comment Utility
0
 
LVL 24

Expert Comment

by:johanntagle
Comment Utility
No please don't notify me of your new questions this way.  I will see them if and when I see them.  If I don't, there are many experts on E-E who can help you.  FYI I will already unmonitor this thread, and I won't get notified of any more posts from now on.
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Having just graduated from college and entered the workforce, I don’t find myself always using the tools and programs I grew accustomed to over the past four years. However, there is one program I continually find myself reverting back to…R.   So …
The goal of the tutorial is to teach the user how to use functions in C++. The video will cover how to define functions, how to call functions and how to create functions prototypes. Microsoft Visual C++ 2010 Express will be used as a text editor an…
The viewer will learn how to clear a vector as well as how to detect empty vectors in C++.

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now