On 2009 Jan 3, at 14:24, Jeff Mar wrote:
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?
= = = = = =
Well, without getting into your specific application, here are the
rules of normalization generally:
= = = = = =
1. Eliminate repeating groups.
Make a separate table for each set of related attributes, and give
each table a primary key.
2. Eliminate redundant data.
If an attribute depends on only part of a multi-valued key, remove it
to a separate table.
3. Eliminate columns not dependent on key.
If attributes do not contribute to a description of the key, remove
them to a separate table.
4. Isolate independent multiple relationships.
No table may contain 2 or more 1:n or n:m relationships that are not
directly related.
5. Isolate semantically related multiple relationships.
There may be practical constraints on information that justify
separating logically related many-to-many relationships.
= = = = = =
If you follow all of the 1st 3 rules, you will have achieved what's
known as "3rd normal form". It's considered to be good enuf for
almost all real-world applications. Going all the way to 4th normal
form or 5th normal form make for interesting academic exercises, but
probably only the obsessive-compulsive do them outside of the classroom.
Tho I generally discourage ANY interaction with the adjudicated
monopoly that is Microsoft, they do have a pretty good, brief
overview of normalization at http://support.microsoft.com/kb/283878.
The way I usually explain it to my clients is that anything that
comes "1 to a customer" goes into that customer's record; everything
that can come in batches of >1 to a customer goes elsewhere; nothing
ever, ever, ever, ever, ever gets recorded in >1 place. Enter a datum
once, and every other place that needs that datum gets it by looking
in that 1 and only place where it lives.
An example. Every person has an address, right? Wrong! Every person
has multiple addresses: home, work, vacation, last year's, ex-
spouse's, etc. Bingo! Separate table for addresses. John, Mary,
Bobby, and Susie all have the same home address. Do they each get a
separate record in the "Addresses" table? Nope, they each get a link
(thru a "join" table) to the SAME address record; a byproduct of such
a link is that, in the record for each of them, you can call up a
portal that shows everybody associated with that address.
OK, now the family moves. Do you go into the "Addresses" record for
them and change the info to their new address? Nope. Then you will
have lost the info about what their OLD address was. (Have I
mentioned that you should never destroy data?) Instead, create a
record for the new address and link them all to it via a "join" table
that includes the word "home" in the "Address Type" field and an auto-
entered "Timestamp Created" field. That "join" table will still
contain links from John, Mary, Bobby, and Susie to the old address,
but those should be tagged with a timestamp for the date on which
they became obsolete.
You can extrapolate from this to the various medical data you'll need
to track.
= = = = = =
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?
= = = = = =
You could, and in the case of the address example I cited above, you
probably should. However, FMP lets you sort portal data in descending
chronological order, and that will probably suffice for things like
height, weight, visual acuity, date of clinic visit, etc. Other folx
on this forum may wish to weigh in here.
= = = = = =
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.
= = = = = =
I'm not sure what you mean by "multiple iterations of a record with a
single change in it", but it's waving red flags at me. Do you mean
something like "Duplicate this client's exam record and then record
in it the fact that I took her temperature today and it was 99.1°."?
Please explain your concern more fully.
= = = = = =
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.
= = = = = =
I don't make much use of calculated keys myself, so I'm going to pass
on this one so that somebody with more experience in that area can
chime in.
FWIW, Jeff, you are asking the right kind of questions.
