to Normalise or not to Normalise - that is the question (1 Viewer)

[Dennisk]

In general I design my DBs to 3rd NF. As I've worked in the Health Service for nearly 30 years, most DBs follow a pattern and I automatically normalise.

I recently worked on a DB with a large dataset. This db collected Obesity monitoring data and this data was taken at intervals over a year.
Originally I kept the initial monitoring data with the basic referral information and the remaining dataset was in another table linked to the referral.
However the users needed to be able to see the data side by side, so I created a query so they could view 6 and 12 months data on a tabbed page. Then they wanted to input the data on the tabbed page.

Eventually I merged all the repeating? data back into basic referral. The $64,000 question, would you consider these to be repeating groups?

Although the same datset is repeating it does relate to 4 episodes spread over a year and the users are over the moon with the changes as it makes it easier for them to input and view data, and
it seems to be easier to report on.

I have a similar project coming up for Childhood Obesity. but here I need to record several 100 data items spread over a year so I want to get this correct first time through. Although here I was thinking about using a seperate table for each monitoring point. This would mean each referral has about 12 tables of data.

 

[The_Doc_Man]

Normalization suggests that the time-coded data per person must be in a separate table listing person and date as compound primary keys.

Since you know about normalization I won't bother you with the shorthand for a one-many table between person and person's weight history.

In answer to your question, "YES". Repeating group it is. If it SHOULD be date-related but isn't, then the only other way to store it is as a sliding window repeating group. To which I would say {gag}{retch}{puke}.

You really NEED to normalize this stuff ASAP. Don't you already see the growth in demand of capabilities? (First, parallel comparisons. Next, ranged data entries. What will be after that?) The only way you won't go nuts trying to do this is to normalize it and be done with it.

Normalization in this case is a form of organizing what you are doing. If you DON'T organize it correctly, how will you ever keep up with ever-changing requirements?

 

[Dennisk]

Hi DocMan,

Had a good think about this problem in the bath on Friday night, and yes you are right, I will just have to make sure that each measurement goes in the correcr order. So each measurement will require a date so I can organise the records in the correct order within the referral. I was thinking of using a sequence number, but I'me sure a case will come up where a child misses one appointment and that sequence is blank.