I need to describe a set of studies that are practiced to a patient, every patient have a "Clinical History", that are described in another table called "Clinical History Lines"(hclines)
Every visit of the patient to the professional means new "Clinical History Lines", where I need to store some information about the "Diagnostic", "history of present illness", and a set of "physical examinations" (fisicalex)
that set of "physical examinations" are described in another table called "Studies", each one could have it's own result value, not all the "Studies" are necessary
me and a friend have been discussing best way to store this data, here are the two approaches.
Here I have the results of the studies described in their own columns.
where I have many "one-to-one" relationships, each value is a column of the table.
2.- http://sqlfiddle.com/#!2/52a444/7 where I have a table called "studies, and another table where I store the "studies" that are practiced with their results.
my doubts are, in example 2, what happens when every patient have to make every study, and with all the professional in the hospital, I'm gonna end with 3.000.000 records in the first week, and I don't know if this gonna slow down the entire app
in example 1, there's no problem with storing null data, but if I need to add another study I'm gonna need to schedule a migration to add another column.
what is the best performing design?, if you have another suggestion I'm open