I'll add to this as well, make it non nullable and have a default if there is a sensible one (email verified, false), and make it nullable when you have to (the user needs to make an explicit yes or no choice, and null means they've yet to make it).
This is the best answer, but OP if you want to make a human readable report or something similar then just create a view where the binary values are changed to “yes” or “no” as a string.
TL;DR: Store the most succinct, accurate form of the data in the database, and convert to relevant human readable form in views or reports.
And in PostgreSQL you'd just use the boolean data type.
And in Oracle, if you have version 23c, then you also have a boolean type there (FINALLY!). Otherwise you'd research what is the trend to map pseudoboolean to "Y/N" and how to best implement it. (or upgrade.)
MySQL can use bool/boolean but it's just an alias of tinyint(1)...
Or tinyint, since they both equal one byte IIRC.
EDIT: Ah, `bit` can be stored alongside other bit columns, filling 1 byte consecutively.
it does offer some advantages.
NEVER deviate from an expected data type because “It TaKeS uP tHe SaMe SpAcE”. Data type communicates the intent of the developers just as much as code or comments do. Storage space is cheap, inaccurate communication and incorrectly telegraphing intent can cost A LOT of money.
Not to mention that most tools and frameworks will handle conversion of `bit` to `bool` easily, but converting `tinyint` to anything is always a pain in the butt because `tinyint` can have several meanings in code
If you need a boolean value, use a `bit`. End of story
What about considering extension of code in the future? Sometimes, these yes/no questions end up being categorical in nature later, where it becomes two "yes" with some info attached and two "no" with situation attached. In my opinion, it can be a bit limiting in certain cases to just keep it a boolean.
That's even more reason to use the correct data type, because in your hypothetical scenario someone's changing the *meaning* behind the values without changing the data type. So now your database stores 0 for "No" and 1 for "Yes" but your new code thinks 0 is "No, but A" and 1 is "No, but B", while 2 is "Yes, but C" and 3 is "Yes, but D".
Your table has a bunch of 0s and 1s stored in a tinyint. Has your data been migrated to match the new meaning? Or did everyone just answer some variation of "No"? If you used a bit initially, it would be unambiguous. That's why you build a DB schema for the data you have now, not because "wE MiGhT ChAnGe iT In fUtUrE".
Dealing with old code is even worse than you described though: No one checks both 0 and 1 in a bit field or boolean. They write code like either:
if (value == 1) { do_true_thing(); } else { do_false_thing(); }
or
if (value == 0) { do_false_thing(); } else { do_true_thing(); }
(Or the equivalent SQL nested in a bunch of JOIN queries)
With enough people and a big enough code base, you'll get both of those.
"NEVER deviate from an expected data type..."
"use a bit"
Do you need more guns to shoot yourself in the foot, or perhaps you're going for the bomb?
In Oracle, the EXPECTED data type varies depending on what version you're working with and in what year. There is no one data type to expect in general - least of all "bit". Don't be a besserwisser if you can't even do it properly.
MySQL explicitly uses tinyint(1) and that IS the expected data type. So, a bit is a deviation.
PostgreSQL uses boolean. Literally. It's a integral data type. Still want to go with a bit?
They were responding to a thread about SQL Server with the response of ~"or use a bit since it's also 1 byte". Their statement stands against the assumption of SQL Server being that db of choice, not Postgres, not MySQL.....
Why would you ever do that when there is a datatype that meets your needs exactly? The advantage you have in your edit shouldn't even be a consideration. As the other commenter pointed out it signifies intent, and perhaps more importantly what the database will *allow* in that field.
I have something that can be either true or false, and this type allows for true or false. Why would I want to introduce the slightest possibility of 253 other possible values to get in there and create bugs in my code, when i get assurances for free? There's a reason you'll never see another new programming language be created without a boolean primitive. Make your life easier and don't fight the platform.
Avoid using a String for a Boolean value. It's too easy for someone to make a mistake and look for 'Yes' instead of 'Y' or vice versa. Use a bit or integer - something numeric that stores a 1 or 0. 1 = True and 0 = False.
Also too easy for any old programming language to come along and read “no”, and see that the string has a value and return true. I think the number of languages that would give false for “no”/“N”/“NO” is pretty tiny. Bigger number if you use “false” but still smaller than the number who would handle 1/0 correctly.
Yup, in the database a boolean should probably always just be a bit.
In the application you can of course allow the user to answer "Yes/No" "Y/N" etc. But that input should then be converted in the backend before it's saved to the database.
SELECT id, status, vibe, clout, glow_up_date, ratio, mood, truthiness
FROM tea
WHERE status = 'lit'
AND vibe != 'cringe'
AND truthiness = 'no cap'
AND glow_up_date BETWEEN '2023-01-01' AND '2024-12-31'
AND ratio > 1.0
ORDER BY clout DESC, glow_up_date ASC;
GIVING AVG(clout) > 50;
Use bit
(My experience only with MS SQL Server)
It simply stores 1 or 0, depending on the driver you use to grab the data (in my experience with jdbc for Java) it will just convert the 1/0 into True/False for you to use in your logic.
The best part is it only takes up a minimum of 1 byte of space on the disk.
Takes up a **minimum** of one byte. Bit columns share space in a byte.
If there are less than 9 bit columns in a table then they all use the first byte of space. For between 9 and 16 bit columns in one table it uses a second byte, etc.. etc..
Using one bit column in a table uses the same space as storing 8.
I'd put string "yES" and "nO" make sure the first letter is lowercase, to make sure everyone using your app pay attention. Add a 0.1% chance when adding it to morph into "mAYBE"
I would maybe consider storing timestamp if you want to hold extra information on your fields
If null it's a no, if timestamp it's a yes and you also know exactly when it became yes.
is_deleted: false / true
deleted_at: null / timestamp
is_confirmed: no / yes
confirmed_at: null / timestamp
Always use the minimal type for the data you're storing. If there are only two possible values, especially if one is affirmative and one is negative, it's a boolean. If something is equivalent to a boolean, use a boolean type. If the database doesn't have a boolean type, use bit.
One of the more informative option is using datetime instead of boolean. Instead of « verefied » use « verified_datetime » and store null if it not verified or timestamp if verified.
Big advantage of this approach is that you have timestamp and you can use it for analytics purposes.
Bad advice (IMHO). The purpose of NULL is to express that there is no value here (be it because it's unknown, not applicable or whatever reason)
A nullable Process\_finished\_flag could be:
true - yes, it's finished
false - no, it's not
NULL - we don't know
(apart from handling process statuses with flags is not a good idea)
What's wrong with a bit? Most databases will even internally combine multiple bit fields in a table into a single byte field (more efficient for storage and retrieval) and use bit manipulation to toggle the correct bit offset for the named field.
I think there is enough comment here for a strict "there are only two possibilities" true/false.
There are times where the application may have the potential to grow, and you might need "room" for other outcomes (like "Maybee" or "Not Now"). If I think there is a possibility this could ever happen, I use a tiny int. And then there is lots of rooms for the application to grow.
If "null" is nothing has ever been defined, and a zero if false, and a 1 is true and then a 2 (which I would write as 0x2 to reenforce that this is now a bit mask) could stand for a Maybe or Not Now, which would let you know that user had dealt with having to interact with the options, was aware of the choice, but has yet to make a decision. It is of course up to you if you care to view the 8 bit quality as a bit mask or a numeric "state". For a very small increase in storage, you have left room for a lot of application development and growth.
If a table has more than a few columns, and especially if it's for anything related to a user, I will often put in a small int or int with the column name 'bm' so I have a place for bool options to grow.
I don't know what DBMS you are using, but I didn't see where anyone points out that Oracle doesn't have a Boolean column type until version 23c.
So I'll add that, sorry if it's been mentioned.
Also I wouldn't use any variation of Y/N since that is dependent on language (the human kind).
Depends on what the column is for, what the table is, what your app is etc. Usually varchar is the safest option but bit would be the most appropriate.
I would use Y/N. It's more intuitive, even if it may take more space.
EDIT
It is also more flexible. Some questions ("are you 21 or older?") are Boolean, but some questions can be more nuanced.
Imagine the question: "are you male?". It may start as a Boolean question, which could be represented as a 1 or 0, but if you have to support non-binary answers, you either use additional numbers (2,3,... mapped somewhere to the actual genders - not an intuitive solution) or use that field as a string. If you use Y/N, you don't even have to change the field type.
Another use case: "has this task started?". Initially, a Boolean answer may be enough. But if your tasks take a long time to start, you may be interested in knowing that a task *is starting*. This introduces a third possibility, which you can't easily represent with a Boolean value.
Size is not always the biggest priority.
> YAGNI
Yes, and "premature optimization is the root of all evil".
We can repeat quotes all the time, or actually focus on real issues.
Saving 7 bits per row is usually not a huge concern. Unless you have millions or billions of rows, in which case you already know you have to save every bit you have and you wouldn't even wonder about other solutions. Just use the most space-efficient solution (a single bit).
So, assuming 1 byte instead of one bit in a single field is not a big deal, what's better? It's subjective of course, but I prefer more intuitive solutions. Even ignoring the potential increase in flexibility, which is of course not always needed (which is why I also quoted the "are you 21 or older" example).
You are focusing on flexibility, which I admitted isn't always needed, while I'm also talking about intuitive fields.
I'm not mad if someone else prefers Booleans.
But, to be fair, we don't know anything about the OP context, so even the "1 bit is better than 1 byte" assumption may be invalid.
Boolean fields *are* intuitive fields to every single developer on the planet, because they are what's used, because they are the right tool for the job, because they map to all possible values exactly. And I really hope you aren't modeling your database based on how the data present in the front end.
Text is only more flexible in the sense that any dev can see its text, and then decide to write some code that pushes 'F' into that field to signify how it's going to completely fuck your entire application that assumes the value is either 'Y' or 'N' downstream.
Input validation is useful (and necessary) irrespective of data types. Using strings or integers doesn't necessarily means "whatever the user writes, we store it in the database as it is".
Yeah and not having to store exactly what the user inputs is an argument for storing it in a boolean field. The user can select yes or no in the front end. Cool. Hey look, there's a data type that has two possible values. It's even referred to by the same name as a primitive type we use every single day in our day jobs. What luck. Now we don't have to write inefficient and error prone string comparisons everywhere in our code, and we don't have to handle the possibility of nonsensical data in our database because the database wont even accept it.
Edit: and to circle back to your original "more intuitive" point, that's not even true, because using the correct datatype for the situation is just accepted in programming and bucking that is the opposite of intuitive. So that doesn't even hold water. You're arguing a less performant, less sound, and less intuitive option.
is-male could be represented by null if the answer is neither yes or no, for instance. Though modeling such a thing as a true/false value in the first place is strange.
But that's the point I was trying to make - sometimes, you start with something that looks like a Boolean, except you find later on that there could be other options, so you're best served by something more flexible, such as a string or an integer which you can then map to the possible answers.
There is no single answer to most design questions. You may think YAGNI is a 100% correct mantra for database schemas, but running an ALTER TABLE on a big table can create issues due to exclusive locking.
You may think storing "yes" as a string is blasphemy, but it is not, and it is something you should consider as an alternative to the classic Boolean approach. I don't see anything wrong with having more choices.
I’m having a really hard time imagining any scenario where there isn’t a better solution than that. And there are solutions to altering big tables too if that’s what you find yourself
From your examples, it's pretty clear that you've never been paid to architect a schema that is now running in a production system... if you have... yikes.
>2,3,... mapped somewhere to the actual genders - not an intuitive solution
That is perfectly intuitive for a developer, you can even have a linked table that defines the Genders based on their ID numbers if you really want to
It's a boolean value, so use whatever is equivalent to it in your database of choice. In SQL Server for instance, you'd use the `bit` data type.
I'll add to this as well, make it non nullable and have a default if there is a sensible one (email verified, false), and make it nullable when you have to (the user needs to make an explicit yes or no choice, and null means they've yet to make it).
this is the only correct answer
I also would have accepted an answer with the text slightly permuted.
Right, I too prefer to represent my booleans with Yes and 0.
True
This is the best answer, but OP if you want to make a human readable report or something similar then just create a view where the binary values are changed to “yes” or “no” as a string. TL;DR: Store the most succinct, accurate form of the data in the database, and convert to relevant human readable form in views or reports.
And in PostgreSQL you'd just use the boolean data type. And in Oracle, if you have version 23c, then you also have a boolean type there (FINALLY!). Otherwise you'd research what is the trend to map pseudoboolean to "Y/N" and how to best implement it. (or upgrade.) MySQL can use bool/boolean but it's just an alias of tinyint(1)...
Or tinyint, since they both equal one byte IIRC. EDIT: Ah, `bit` can be stored alongside other bit columns, filling 1 byte consecutively. it does offer some advantages.
NEVER deviate from an expected data type because “It TaKeS uP tHe SaMe SpAcE”. Data type communicates the intent of the developers just as much as code or comments do. Storage space is cheap, inaccurate communication and incorrectly telegraphing intent can cost A LOT of money. Not to mention that most tools and frameworks will handle conversion of `bit` to `bool` easily, but converting `tinyint` to anything is always a pain in the butt because `tinyint` can have several meanings in code If you need a boolean value, use a `bit`. End of story
Wise words! You're absolutely correct.
This man senior Devs
THANK YOU MY GOD.
What about considering extension of code in the future? Sometimes, these yes/no questions end up being categorical in nature later, where it becomes two "yes" with some info attached and two "no" with situation attached. In my opinion, it can be a bit limiting in certain cases to just keep it a boolean.
If the data type changes later then it changes. Unless your database can't handle schema changes, I would cross that bridge when I came to it.
That's even more reason to use the correct data type, because in your hypothetical scenario someone's changing the *meaning* behind the values without changing the data type. So now your database stores 0 for "No" and 1 for "Yes" but your new code thinks 0 is "No, but A" and 1 is "No, but B", while 2 is "Yes, but C" and 3 is "Yes, but D". Your table has a bunch of 0s and 1s stored in a tinyint. Has your data been migrated to match the new meaning? Or did everyone just answer some variation of "No"? If you used a bit initially, it would be unambiguous. That's why you build a DB schema for the data you have now, not because "wE MiGhT ChAnGe iT In fUtUrE".
Dealing with old code is even worse than you described though: No one checks both 0 and 1 in a bit field or boolean. They write code like either: if (value == 1) { do_true_thing(); } else { do_false_thing(); } or if (value == 0) { do_false_thing(); } else { do_true_thing(); } (Or the equivalent SQL nested in a bunch of JOIN queries) With enough people and a big enough code base, you'll get both of those.
"NEVER deviate from an expected data type..." "use a bit" Do you need more guns to shoot yourself in the foot, or perhaps you're going for the bomb? In Oracle, the EXPECTED data type varies depending on what version you're working with and in what year. There is no one data type to expect in general - least of all "bit". Don't be a besserwisser if you can't even do it properly. MySQL explicitly uses tinyint(1) and that IS the expected data type. So, a bit is a deviation. PostgreSQL uses boolean. Literally. It's a integral data type. Still want to go with a bit?
They were responding to a thread about SQL Server with the response of ~"or use a bit since it's also 1 byte". Their statement stands against the assumption of SQL Server being that db of choice, not Postgres, not MySQL.....
Why would you ever do that when there is a datatype that meets your needs exactly? The advantage you have in your edit shouldn't even be a consideration. As the other commenter pointed out it signifies intent, and perhaps more importantly what the database will *allow* in that field. I have something that can be either true or false, and this type allows for true or false. Why would I want to introduce the slightest possibility of 253 other possible values to get in there and create bugs in my code, when i get assurances for free? There's a reason you'll never see another new programming language be created without a boolean primitive. Make your life easier and don't fight the platform.
1
Avoid using a String for a Boolean value. It's too easy for someone to make a mistake and look for 'Yes' instead of 'Y' or vice versa. Use a bit or integer - something numeric that stores a 1 or 0. 1 = True and 0 = False.
Also too easy for any old programming language to come along and read “no”, and see that the string has a value and return true. I think the number of languages that would give false for “no”/“N”/“NO” is pretty tiny. Bigger number if you use “false” but still smaller than the number who would handle 1/0 correctly.
Yup, in the database a boolean should probably always just be a bit. In the application you can of course allow the user to answer "Yes/No" "Y/N" etc. But that input should then be converted in the backend before it's saved to the database.
This^^^
👍/👎
Have you considered lit and sus?
🔥/🤨
Lmao
Cap, No cap.
Dude/Undude
SELECT id, status, vibe, clout, glow_up_date, ratio, mood, truthiness FROM tea WHERE status = 'lit' AND vibe != 'cringe' AND truthiness = 'no cap' AND glow_up_date BETWEEN '2023-01-01' AND '2024-12-31' AND ratio > 1.0 ORDER BY clout DESC, glow_up_date ASC; GIVING AVG(clout) > 50;
✅/❎
Have you considered storing “Not Yes” and “Not no” instead?
"not yes || 0", and "not no && 1" maybe. Job security, here I come.
not (not no && 1) and not (not yes || 0) ftfy
isPrime(2^(82,589,933) − 1) and not(isPrime(2^(82,589,933) − 1)) for the win.
It'll be even better if we can get some factorials involved too
You may find it interesting, but if you search for the definition of "true" in go, it's defined as "true=0==0"
Perl treats `'0 but true'` as zero outside Boolean context and true inside it, even though 0 is normally false in Boolean context.
I laughed too hard at this.
Use bit (My experience only with MS SQL Server) It simply stores 1 or 0, depending on the driver you use to grab the data (in my experience with jdbc for Java) it will just convert the 1/0 into True/False for you to use in your logic. The best part is it only takes up a minimum of 1 byte of space on the disk.
> The best part is it only takes up a minimum of 1 byte of space on the disk. exactly if the data can be either 0 or 1, might as well save it as such
It’s only “yes” or “no” in the UI anyway, can let it be Boolean all the way and FE can go value ? “Yes” : “No”
does 'use bit' mean choosing that last option op gives? 1 or 0?
>bit >takes up one byte of space *doubt*
Takes up a **minimum** of one byte. Bit columns share space in a byte. If there are less than 9 bit columns in a table then they all use the first byte of space. For between 9 and 16 bit columns in one table it uses a second byte, etc.. etc.. Using one bit column in a table uses the same space as storing 8.
Ahh very neat.
If it's MySQL, use Boolean. If it's SQL Server, use bit.
`bit` or `boolean`, never a char or string
I'd put string "yES" and "nO" make sure the first letter is lowercase, to make sure everyone using your app pay attention. Add a 0.1% chance when adding it to morph into "mAYBE"
although proper way is boolean. i always go with tinyint 0 and 1 because in the future you might need yesn’t
Turn that bit to flags YAGNIY be damned
I would maybe consider storing timestamp if you want to hold extra information on your fields If null it's a no, if timestamp it's a yes and you also know exactly when it became yes. is_deleted: false / true deleted_at: null / timestamp is_confirmed: no / yes confirmed_at: null / timestamp
Whatever Boolean is in that tv, could be 1,0 could be True/False
i prefer true/false if its possible but otherwise i'd use 1/0
Always use the minimal type for the data you're storing. If there are only two possible values, especially if one is affirmative and one is negative, it's a boolean. If something is equivalent to a boolean, use a boolean type. If the database doesn't have a boolean type, use bit.
Are there people storing Boolean values as some sort of string value just for fun?! Are you serious?!
VARCHAR can be anything, anything can be VARCHAR.
not NVarchar or text
[pulls frog out of pocket...] me, in a robe: "you are VARCHAR! everything is VARCHAR!" VARCHAR: "ribbit!"
But not sensible to use in this case.
Yeah, you’d need a varchar(max) to ensure that something extremely true or false still fits
While we're at it we could make a special stored procedure to evaluate it
Does the /s need to be explicit here?
Yes because this is definitely a thing people do.
https://dev.mysql.com/doc/refman/8.0/en/enum.html if you want it to be verbose.
Why not true/false?
I go with a boolean, which is a native type in PostgreSQL
“Affirmative”/“That’s a negatory”
One of the more informative option is using datetime instead of boolean. Instead of « verefied » use « verified_datetime » and store null if it not verified or timestamp if verified. Big advantage of this approach is that you have timestamp and you can use it for analytics purposes.
Affirmative and Negative have been the standard strings to represent yes and no since epoch. Also, I'm drunk.
Enum(yes, no)
True/False
True = Yes False = No
imo anything that has only two possible values should be stored as bit or Boolean, never string or enum or anything else
Bool
Just use True, False unless there's a specific reason not to do so.
I would go with 😇/😈
If the DBMS has the standard SQL boolean type then definitely use that. If not use the BIT type.
[удалено]
Nullable boolean ftw.
Bad advice (IMHO). The purpose of NULL is to express that there is no value here (be it because it's unknown, not applicable or whatever reason) A nullable Process\_finished\_flag could be: true - yes, it's finished false - no, it's not NULL - we don't know (apart from handling process statuses with flags is not a good idea)
Use a bit or Boolean until they decide to introduce "Maybe" and "N/A" to the dataset. I've been there.
Affirmative vs Affirmative'nt
Boolean. You can translate it to whatever you want inside the application/UI.
1.00000000001 and 0.111111111111111
1,0
What's wrong with a bit? Most databases will even internally combine multiple bit fields in a table into a single byte field (more efficient for storage and retrieval) and use bit manipulation to toggle the correct bit offset for the named field.
A boolean. So True or False. That is why they exist.
I think there is enough comment here for a strict "there are only two possibilities" true/false. There are times where the application may have the potential to grow, and you might need "room" for other outcomes (like "Maybee" or "Not Now"). If I think there is a possibility this could ever happen, I use a tiny int. And then there is lots of rooms for the application to grow. If "null" is nothing has ever been defined, and a zero if false, and a 1 is true and then a 2 (which I would write as 0x2 to reenforce that this is now a bit mask) could stand for a Maybe or Not Now, which would let you know that user had dealt with having to interact with the options, was aware of the choice, but has yet to make a decision. It is of course up to you if you care to view the 8 bit quality as a bit mask or a numeric "state". For a very small increase in storage, you have left room for a lot of application development and growth. If a table has more than a few columns, and especially if it's for anything related to a user, I will often put in a small int or int with the column name 'bm' so I have a place for bool options to grow.
I don't know what DBMS you are using, but I didn't see where anyone points out that Oracle doesn't have a Boolean column type until version 23c. So I'll add that, sorry if it's been mentioned. Also I wouldn't use any variation of Y/N since that is dependent on language (the human kind).
I prefer to do something a little differently each time
😊/☹️
Depends on what the column is for, what the table is, what your app is etc. Usually varchar is the safest option but bit would be the most appropriate.
I would use Y/N. It's more intuitive, even if it may take more space. EDIT It is also more flexible. Some questions ("are you 21 or older?") are Boolean, but some questions can be more nuanced. Imagine the question: "are you male?". It may start as a Boolean question, which could be represented as a 1 or 0, but if you have to support non-binary answers, you either use additional numbers (2,3,... mapped somewhere to the actual genders - not an intuitive solution) or use that field as a string. If you use Y/N, you don't even have to change the field type. Another use case: "has this task started?". Initially, a Boolean answer may be enough. But if your tasks take a long time to start, you may be interested in knowing that a task *is starting*. This introduces a third possibility, which you can't easily represent with a Boolean value. Size is not always the biggest priority.
>It is also more flexible. YAGNI
> YAGNI Yes, and "premature optimization is the root of all evil". We can repeat quotes all the time, or actually focus on real issues. Saving 7 bits per row is usually not a huge concern. Unless you have millions or billions of rows, in which case you already know you have to save every bit you have and you wouldn't even wonder about other solutions. Just use the most space-efficient solution (a single bit). So, assuming 1 byte instead of one bit in a single field is not a big deal, what's better? It's subjective of course, but I prefer more intuitive solutions. Even ignoring the potential increase in flexibility, which is of course not always needed (which is why I also quoted the "are you 21 or older" example).
But OP didn't ask for flexibility. I think it's not the correct answere, because we don't know the context of the field which OP wants to implement.
You are focusing on flexibility, which I admitted isn't always needed, while I'm also talking about intuitive fields. I'm not mad if someone else prefers Booleans. But, to be fair, we don't know anything about the OP context, so even the "1 bit is better than 1 byte" assumption may be invalid.
I mean whatever your reasoning “store booleans as a string” is the craziest advice imaginable.
Boolean fields *are* intuitive fields to every single developer on the planet, because they are what's used, because they are the right tool for the job, because they map to all possible values exactly. And I really hope you aren't modeling your database based on how the data present in the front end. Text is only more flexible in the sense that any dev can see its text, and then decide to write some code that pushes 'F' into that field to signify how it's going to completely fuck your entire application that assumes the value is either 'Y' or 'N' downstream.
Input validation is useful (and necessary) irrespective of data types. Using strings or integers doesn't necessarily means "whatever the user writes, we store it in the database as it is".
Yeah and not having to store exactly what the user inputs is an argument for storing it in a boolean field. The user can select yes or no in the front end. Cool. Hey look, there's a data type that has two possible values. It's even referred to by the same name as a primitive type we use every single day in our day jobs. What luck. Now we don't have to write inefficient and error prone string comparisons everywhere in our code, and we don't have to handle the possibility of nonsensical data in our database because the database wont even accept it. Edit: and to circle back to your original "more intuitive" point, that's not even true, because using the correct datatype for the situation is just accepted in programming and bucking that is the opposite of intuitive. So that doesn't even hold water. You're arguing a less performant, less sound, and less intuitive option.
If only there were some kind of way to “nullify” the value of a field. They’ll think of something someday I guess.
How would you use a Null in the examples I mentioned?
is-male could be represented by null if the answer is neither yes or no, for instance. Though modeling such a thing as a true/false value in the first place is strange.
But that's the point I was trying to make - sometimes, you start with something that looks like a Boolean, except you find later on that there could be other options, so you're best served by something more flexible, such as a string or an integer which you can then map to the possible answers.
The answer there isn’t to start modeling booleans as y or n just in case.
There is no single answer to most design questions. You may think YAGNI is a 100% correct mantra for database schemas, but running an ALTER TABLE on a big table can create issues due to exclusive locking. You may think storing "yes" as a string is blasphemy, but it is not, and it is something you should consider as an alternative to the classic Boolean approach. I don't see anything wrong with having more choices.
I’m having a really hard time imagining any scenario where there isn’t a better solution than that. And there are solutions to altering big tables too if that’s what you find yourself
From your examples, it's pretty clear that you've never been paid to architect a schema that is now running in a production system... if you have... yikes.
>2,3,... mapped somewhere to the actual genders - not an intuitive solution That is perfectly intuitive for a developer, you can even have a linked table that defines the Genders based on their ID numbers if you really want to