I will be developing an Access application that will contain patient and medical procedure information. There are about 20 different procedures that can be done. For each procedure, we will report the date, patient number, blood pressure, weight, etc. but then each procedure has several measurements that are not common to any of the other procedures. Do I create a separate table for each procedure (with link to patient ID) or should I create a procedure table with all the measurements? I'm not sure which is better. Separate tables would require union queries for reporting/looking up the "whole" patient but one table would contain many fields that would be null in most cases.
I think an analogy would be a product table. If I sell computers (with fields for processor, memory, etc.) and hiking shoes (with fields for size, color, etc.), should I keep them in one product table or should I make a table for computers and a table for hiking shoes (or some other variation)?
I am not new to database application development but have usually worked with databases that were already designed. I have read several of your other postings regarding normalization, etc. but none seems to address this issue.
Thank you in advance for your help.