0

I am curious if there are any major differences with performance and/or conventionality regarding which binary data types one chooses. Let's say I have a table of metallic objects and I am considering to create a binary field to record whether or not the object is reflective.

reflective BIT NOT NULL DEFAULT 0 -- 0 for nonreflective 1 for reflective
reflective CHAR(1) NOT NULL DEFAULT 'F' -- False for nonreflective, True for reflective
reflective ENUM('N','R') NOT NULL DEFAULT 'N' -- N for nonreflective R for reflective.
reflective ENUM('nonreflective','reflective') DEFAULT 'nonreflective' -- I think this takes up the same amount of storage resources but is clearer

Question: ENUM aside, they all seem more or less equal in terms of readability, and while I'm not exactly sure which would make for most efficient scaling and querying, I'd venture they would all be comparable. Am I right in this assumption; is the binary data structure largely arbitrary, or are there trade-offs I should be aware of?

Also I'd be interested to know if among these examples (or structures I did not include) there is a clear convention, and why.

  • you've missed BOOL which is effect TINYINT which seem like a much more natural type. CHAR size will depend on character set. I suspect you're right, it doesn't matter too much. – danblack Aug 25 '18 at 5:44
  • @danblack I'll note those data types too, thanks for pointing that out – Arash Howaida Aug 25 '18 at 5:51
  • 1
    I think char(1) is far the worst one. What does an 'X' or a 'U' mean? And a ' ', a '*'? True? False? You'd need a check constraint to prevent nonsense values (which don't work in MySQL prior 8 AFAIK). Unless there's a genuine Boolean type, I'd choose bit to represent a Boolean, that's close enough. But in your particular case, you want to have in mind, that one day you might want to extend that logic, like different grades of reflectiveness. I guess with that in mind, an enum is a good choice depending on how probable such an extension is. – sticky bit Aug 25 '18 at 5:58
  • I'm curious how do you think to use boolean XOR for those T/F like T xor F. In mysql boolean operators returns 0 for false and non-zero for true. You should use the same convention unless you have appropriate wrapper functions that mimic bundled ones. – Kondybas Aug 26 '18 at 10:57
  • There are many debates about ENUM versus the other types -- both in this forum and in stackoverflow.com . – Rick James Sep 20 '18 at 2:15
0

No matter what you choose, you should pick a convention and stick to it.

Here are my recommendations

BOOL/BOOLEAN

If the data values are truly binary, use the BOOL/BOOLEAN data type.

CHAR/VARCHAR

YES/NO works well when you have lazy UI developers that want to show the result with out translating it. The single character value ( Y/N ) works well also.

The downside: Multinational applications will still have to translate the text. (eg はい/いいえ )

NUMBER(1)

For databases that do not support bool/boolean I recommend using a NUMBER(1). This way, you can tell the UI developers that they will need to translate it into to the proper language. Make sure you document what 1 and 0 mean.

enum

I do like the idea of using an enum to store the values. This would allow you to store a greater range (eg somewhatReflective, mostlyReflective)

Proper datatype

I'm assuming the question is about data types whose values can be represented as binary. (eg true/false) This section is here to remind readers: Always pick the proper data type for your column (eg DATE for dates, etc.)

The albedo of an object (relfectiveness) is measured as a dimensionless NUMBER whose value is BETWEEN 0 AND 1. I would not use a BOOLEAN data type for the reflective column.

  • Since the Question refers to MySQL, consider TINYINT, not NUMBER(1). – Rick James Sep 20 '18 at 2:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.