How to display values as Y or N in SQL?

Question

I am creating a database for a small clinic in central America. The database has a "visit" table with dozens of fields for a series of yes or no questions.

I know that the best (simplest, most efficient) way to store binary values in SQL is using the bit datatype (x), however I know I could also use datatypes of char(1) for "Y" or "N" at the tradeoff of using more space (x).

Originally, I had planned on using the bit datatype and storing 1 for yes and 0 for no, however I don't know how to make this display as Y or N in php.

Currently, in a separate table, I have values for HIV and Diabetes stored as 0 or 1 and I perform an inner join in my SQL select statement to two separate tables relating 1 to Y and 0 to N.

If I were to continue with this technique then I would need to make dozens of tables relating 1 to Y and 0 to N. This seems inefficient and very redundant.

Is there a way to relate multiple columns of a table to one table relating 1 to Y and 0 to N? I'm using PHPmyadmin.

Yes, you can have as many columns from as many tables as you want reference the same "lookup" table; there is no special syntax needed. That said, you could also just select such fields with simple expressions rather than going thorough the trouble of a two value lookup table. `SELECT CASE someField WHEN 1 THEN 'Y' ELSE 'N' END AS someFieldYN` — Uueerdo, Dec 22 '17 at 22:01
Also, I probably would not bother with the BIT type. TINYINT may not take noticeably more space (if any more at all), and will likely be much easier to work with. — Uueerdo, Dec 22 '17 at 22:09
If you are going to create an output column of `Y` and `N` values, don't. It is hard to read. Better would be 'Yes' and 'No' -- because they are different widths. Or 'yes' and blank, or blank and 'no'. Or... — Rick James, Dec 28 '17 at 00:31

score 1 · Accepted Answer · answered Dec 22 '17 at 22:28

With respect, you don't need to scramble to save table space for a small clinic. Write your program in the way that's easiest to maintain.

The savings you'll achieve by using bits instead of simpler column types amount, probably, to tens of thousands of bytes. (=nothing measurable).

If you use any flavor of int (tinyint?) you can translate to text in your query.

 SELECT IF(column=1,'Sí','No') column

for example.

score 0 · Answer 2 · answered Dec 22 '17 at 22:11

I would use the boolean data type for saving the state (if you don't want to change that you can use 1 and 0 too) and use php to convert the true or false value to show for true Y and for false N when the user wants to see the result like this:

<?php

 $stateFromDB; //Get it from db
 if($stateFromDB == true){
    echo "Y";
 }else{
    echo "N";
 }

?>

How to display values as Y or N in SQL?

2 Answers2