Hello. I am new to this but here goes...please let me know if this is the appropriate forum.
I am building a database to track clients in a small medical clinic (200 standing patients, 100 new patients a year - outpatient and day treatment). A few design questions: 1. The clients will each have a unique medical record number in a table (MR table). This will be connected to name and other info that doesn't usually change (pt id table). Then, there is info that may change more frequently....address&phone, contact info, insurance info, emergency info for instance. Then there is info that changes daily...labs, vitals, physical exam. For a medical record, clinical info needs to be dated and signed so the daily stuff I see as each type having it's own table and own entry. The MR table obviously should be unique. But, the occasional changing info, would you recommend separate tables? or one table with lots of fields? My business partner has suggested we basically keep track of all the info input i.e. all old addresses, old insurance info, etc so we can track things backward. I have built small databases but not larger databases and therefore I am not sure if this still counts as a really small database in terms of number of records. i.e. 200 outpatients among 5 staff may generate 2 visits for 52 weeks so 200 x 2 x 52 = 20,000 plus 16 patients per day x 250 days = 4000 daily entries, 4000, md notes, 4000 staff notes, 4000 labs. Would you recommend separate or combined tables? Would you create a new record for each change in each table every time someone makes any change? Thanks, Jeff
