A Patient Care Report PCR must capture procedures performed on a given patient. In the system this relates to there are 60 procedures types. Each procedure has the following data elements in common (though not all procedures have all of these elements rather most have a subset):
Each of the above listed elements may hold different data depending on the type of procedure. In addition each procedure type may have additional elements unique to that procedure, or unique to a subset of procedures. Note: Elements E19_07, E19_12, and E19_13 are multiple choice data entry so will break out into separate tables. Some of the unique elements may also be multiple choice data entry, so will need separate tables or allowed as NULL.
The system will receive about 100 PCRs per day with each PCR having data entered on several to a dozen or more procedures that were performed.
It seems to make sense to have a table that holds PCR ID and procedure Code/table (call it ProceduresPerPCR, and a table for each procedure (and additional tables for elements that are multiple choice data entry). A typical query would then get the procedures performed given a particular PCR ID in two steps. Step 1, retrieve from ProceduresPerPCR the procedures per a given PCR ID, and step 2, query each table for the procedure specific element data. This seems preferable to having one large table holding all the procedure data and having lots of columns allowing NULL.
Interested in getting some feedback, before proceeding.