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.
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