As stated in the title, when designing databases, what is the preferred way to handle tables that have multiple columns that are just storing true / false values as just a single either or value (e.g. "Y/N: or "0/1")? Likewise, are there some issues that might arise between different databases (e.g. Oracle and SQL Server) that might affect how the columns are handled?
In SQL Server
, there is BIT
datatype. You can store 0 or 1 there, compare the values but not run MIN
or MAX
.
In Oracle
, you just use NUMBER
or CHAR(1)
.
In MySQL
and PostgreSQL
any datatype is implicitly convertible to BOOLEAN
.
Both systems support BOOLEAN
datatype which you can use as is, without the operators, in the WHERE
or ON
clauses:
SELECT *
FROM mytable
WHERE col1
, which is impossible in SQL Server
and Oracle
(you need to have some kind or a predicate there).
In MySQL
, BOOLEAN
is a synonym for TINYINT(1)
.
In PostgreSQL
too (in terms of storage), but logically, it's not implicitly convertible to any other type.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With