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.