Comparing SQL binary data types

Question

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.

Answer 1

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.

• Rick James: Since the Question refers to MySQL, consider TINYINT, not NUMBER(1).