Richard, That is very helpful for me in terms of deciding to keep data. For the forum
1. What does normalize mean in this context? 2. If each time I add a piece of data, I create a record to time stamp, then should I have a field that keeps track of "active" or most recent change and set the old record to inactive? 3. If I am making multiple iterations of a record with a single change in it, should I use smaller tables (fewer fields) or bigger tables? Does this kind of efficiency (smaller tables mean less duplication of other data) even matter? The smaller tables require better design to pass the info back and forth so more work up front than just a big table of demographics. 4. Will definitely work on the overview screen with most recent data. 5. Do you consider using complex ID's? i.e. 00123.00101 so MR is 00123, with the .001 meaning 1st admit and the .00001 meaning outpatient (vs IOP vs Full day treament)? This passes three pieces of info and then I can use calculations in foreign tables to break it down into three different pieces. It had seemed like this might at times be useful for go to related records as it would differentiate each admission by sorting on a single field and yet I could use the calculated result as a find field when I want by medical record number. This may be more complex than just scripting finds to include multiple criteria. Thanks for the help. Jeff On Sat, Jan 3, 2009 at 11:42 AM, Richard S. Russell <[email protected] > wrote: > Retitled from "Re: Go To related record" > On 2009 Jan 3, at 12:45, Jeff Mar wrote: > > 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? > > > This is the ideal forum for such questions, Jeff. In fact, I'll bet that > almost everybody on this list is grateful to see someone asking these > questions in the design phase of the database, instead of waiting for a > couple of years to try to resolve a flock of problems arising from flawed > original design. > > I have 5 fundamental pieces of advice: > (1) Never destroy data. > (2) Normalize the living daylights out of this puppy. > (3) Never destroy data. > (4) Timestamp absolutely everything. > (5) Never destroy data. > > I think that one thing you'll find you want is an overview screen for each > patient that will pull in all of the MOST RECENT data for her or him. For > sure this would include things like address, phone number, insurance > numbers, height, weight, most recent visit, next scheduled appointment, next > of kin, etc. Hiding behind almost all of these things is a long string of > less-recent data that you should be able to call up with a click. > > You'll also have to spend more time than most database designers working > your way thru security and confidentiality issues. For example, you'll want > your receptionist to be able to look at and revise addresses, phone numbers, > appointments, etc. but probably NOT diagnoses, prescriptions, and so on. > > There are a zillion other considerations on this project, but there are > also a flock of experts on this forum, and they'll all have something to > say. I expect most of them will contribute a bite-sized piece like the > above, so you can digest them in small, non-overwhelming installments. > > > = = = = = = > Richard S. Russell, a Bright (http://the-brights.net) > 2642 Kendall Av. #2, Madison WI 53705-3736 > 608+233-5640 • [email protected] > http://richardsrussell.livejournal.com/ > > = = = = = = > Cheops's Law: Nothing ever gets built on schedule or within budget. >
