Hello Horst, This is an issue we have grappled with a lot.
Thursday, December 29, 2005, 11:37:10 AM, you wrote: HH> On Thu, 29 Dec 2005 11:53, [EMAIL PROTECTED] wrote: >> Having sunk countless hours into this and have very little to show >> for it, personally I'm convinced there must be a better way. HH> The way I took / am taking with mini-gnumed is as follows: HH> the problems / situation: HH> 1.) fully normalized tables proved to be too unwieldly / slow for some HH> purposes - e.g. selecting patients from a list box after entering a name, and HH> you want some demograpohic details (name, age, dob, street, town ...) display HH> in the selector box Yes, speed and normalization are enemies... HH> 2.) denormalized tables can be a pain in the butt when updating data & trying HH> to keep data consistent, but are very easy for reading information quick The other option is to cache the demographics in a text format and return this. We cache the whole HL7 PID segment and return that eg PID|1||104365^^^Medical-Objects&7C3E3682-91F6-11D2-8F2C-444553540000&GUID^SR^PKI Distribution Server 1 &B53C5B54-6B24-49FC-819F-C9848FE4B68D&GUID~2220569^^^Medical-Objects &7C3E3682-91F6-11D2-8F2C-444553540000&GUID^SR^Buderim GE Centre& 7C3E3681-91F6-11D2-8F2C-444553540000&GUID~118700^^^Medical-Objects& 7C3E3682-91F6-11D2-8F2C-444553540000&GUID^SR^Medical Objects&7CE435EE-62C4-44CC-91A9-5C7BF1CB7BAE&GUID ||MCINTYRE^Alana^^^^^L||20010504|F|||4/24 Sutherland Street^^BUDERIM^QLD^4556^AU There is a lot of information in this for display and as its a blob it can be returned quickly. HL7 PID has all the demographics you could ever need! All the data behind it is normalised in tables and the PID segment is rebuilt when needed, which is slow, but as its done in a saving thread it doesn't matter as no one is waiting for it to happen (typically takes a few seconds). We do a similar thing for provider records (STF=STAFF) eg STF|100284444^^Medical-Objects|HH2453000EV^^^Medical-Objects&7C3E3682-91F6-11D2-8F2C-444553540000&GUID ^UPIN~225449DW^^^AUSHICPR^UPIN|HERB^HORST^^^Dr.^^L||||A||False^^MO_ROUTABLE| ^WPN^PH^^61^02^66572255~^WPN^FX^^61^02^66571188|8 Hickory Street^Dorrigo Medical Centre^ DORRIGO^NSW^2453^^O||||||||2311-11^GP||N||N This approach is something we use a lot and its very fast for display but has normalised data behind it. Whenever its changed its updated is real time, but no user waits for that to actually happen as there is a multi-tier messaging system behind it. As we are use HL7 as the sole on the wire format we have a text format that's ready made for this, but the approach remains valid in other cases. HH> 3.) simulating denormalized tables with updateable views is a bit slow for HH> reading purposes, and most access will be read-only HH> possible solution I took: HH> 1.) start with denormalized tables to get things going quickly HH> Model the denormalized tables to the needs of the most commonly / frequently HH> user interface elements to facilitate delay-less user interaction (= data HH> model does not follow theoretical ideas about the nature of the data, but it HH> follows the real world interaction the end user will have with the data) Once you start it can be very like smoking.... HH> 2.) add normalized tables later, with trigger functions in both normalized and HH> denormalized tables doing the housekeeping - wherever needed and appropriate This is an alternative solution, but doesn't work if you need database tables to produce a display (2 tier model). HH> it works and is as fast as it gets, but it is not aesthetically pleasing and HH> not scalable. HH> Horst HH> _______________________________________________ HH> Gpcg_talk mailing list HH> [email protected] HH> http://ozdocit.org/cgi-bin/mailman/listinfo/gpcg_talk -- Best regards, Andrew mailto:[EMAIL PROTECTED] Andrew McIntyre (Medical-Objects) http://www.medical-objects.com.au _______________________________________________ Gpcg_talk mailing list [email protected] http://ozdocit.org/cgi-bin/mailman/listinfo/gpcg_talk
