Using [0,1] versus ["Y","N"] versus ["T","F"] in a logical/boolean database field?

Just out of curiosity and lack of a definite answer...

I was just looking at some data that is sent from a 3rd party to us, and their "Gender" field is a [0,1] denoting either a Female (0) or a Male (1).

Is there any better reason to use [0,1] over ["F","M"]?

Does 开发者_如何学Goit depend on the scenario and the intuitiveness between a field and its value?

Data access speeds and/or size constraints?

If the moon is full?

---

What are the differences in using [0,1] versus ["Y","N"] versus ["T","F"]?

---

It's better to use 0 and 1 instead of F and M if you want to obfuscate your data to make it difficult for other programmers to understand it.

Otherwise, no, there's no advantage.

Also, I can give you a major disadvantage. I'm working on an application that deals with pigs. Male pigs, like some other male animals, are castrated if they're going to be used for food and not breeding, because it improves the meat quality.

The application originally just tracked males and females. But now we need to track three different sexes: male, female, and barrow (the term for a castrated pig). It'd be a pain to change that if somebody had decided to use 0 and 1 in a bit field for the sex.

---

Aren't we overlooking the obvious use -> foreign keys? I know the original question implied a bit field, but if it is truly numerical, might the 0,1 gender column refer to a gender table?

---

A major advantage is that a column that allows for more than two values can be extended naturally if your assumptions change.

Also, more philosophically, our notions of gender/sexuality are much more fluid than accounted for in a binary field. For instance, I was hired to fix a major government application in Massachusetts when same-sex marriage laws were passed, because lots of assumptions were made about marriage that were later invalidated.

---

Not unless you want to get down to the level of bits for each entry. I mean, you could fit 0 or 1 into one bit, while a character takes up 8 bits. For the most part, this isn't worth it.

I think "M" or "F" is clearer, because it provides more semantic information.

---

I'd create a user defined type in sql, or an enum class in c#/vb and store the 0,1 in the database for the previously stated reasons of size and speed.

---

Curiously noboby has mentioned languages.
M/F is okay in English but what about other languages?

Then again you could always argue that another table should be used for lists.
Although here we are creating a complicated solution.

A bit (or boolean) field should only ever be used when there are definitively only 2 choices.

My two cents.

---

It really doesn't matter.

---

If you are really, really, really worried about size constraints, [0,1] will save you a few bits.

---

Well, comparing Ints is a little bit easier than comparing Strings; when comparing strings you have to take uppercase and lowercase into account.

---

The differences in performance are going to be trivial. Go with the more intuitive one for humans M/F.

---

Why not use an enum? That allows you to

1. ensure that you must always use the correct type, potentially reducing errors
2. allow the database to potentially optimize the number of bits used
3. have human readable output for free

---

The short answer is no since a single character takes the same storage space as an integer.

The long answer is it depends on how your application is written. I wrote an app once that had a gender field in the database with 0 or 1 because in the application layer I had an enum that mapped Gender.Female and Gender.Male to the 0 and 1 values, respectively.

---

Well, in SQL Server it would definitely matter. You should use a bit column type in this case (1/0 or True/False -- however you want to say it). That's just 1 bit of storage compared to 1 byte for a char(1).

---

For flags in a record, I prefer "Y"/"N" or "T"/"F" to 1/0.

If you want to phrase the flag as a question, use Y/N makes it clear that "Y" agrees answers the question positively, and "N" means a negative answer e.g.

SHOULD_SPECIAL_DISCOUNT_APPLY - Y or N

If you want to phrase the flag as a positive statement, T/F is clearer. T - means the statement is true, F means the statement is false:

SPECIAL_DISCOUNT_APPLIES - T or F

0 or 1 doesn't have a straightforward mapping to True or False - it depends which way around it's meant. You can't guarantee that '1' means True/Yes and '0' means False/No - it isn't this always that way around in electronics and in software it depends how consistent the programmer has been and how well named the fields are...

---

Since sex is not actually binary - there is a continuous range of 'intersex' conditions between male and female, as well as beings with no sex at all - it's best to use a floating-point type. 0 for female (being the default, at least in mammals), 1 for male, with intermediate values for intermediate conditions, and NaN for those with no value.

But remember, this will never be fully applicable, because there is no type for the human heart. Although complex is often a good approximation.

---

Really depends on database.

• SQL Server uses a bit
• MySQL uses a tinyint
• Storing T/F will be at least a char(1)

---

After reading all these and doing a little research, I've come to the conclusion that:

• [0,1] field is useful because its international and can be expanded upon to include more clauses when linked to a static table of definitions.

• ["Y","N"] and ["T","F"] are probably recognized worldwide, but are related to the English language.

• ["M","F"] gender type fields are English-based as well and restrict usage when considering someone who doesn't want to mention their gender or whose gender is indeterminable (hermaphrodite)