Re: data normalisation UNF to 1NF

johnk wrote:
>i am new to database design and have a medical record database to
>design.I have a problem with a particular set of attributes in that i
>think i will always have a repeating field at 1NF and i am not sure
>how to eliminate this. i have omitted most of the attributes for
>clarity.
>
>Patient_ID is the primary key with all the other attributes related to
>this e.g address, dateofbirth, weight etc.
>
>Med_history_type will be a drop down list for the doctor to choose an
>option e.g hospitalisation, accidents, X_ray. After making the choice
>the reason, date,results and notes will be entered.
>
>The same will happen with Med_current_type with a drop down list
>choice of cardiovascular, respiratory, neurological etc being picked
>and then notes added.
>
>The problem i have is that if the doctor picks hospitalisation and
>fills in the details and then chooses accidents and fills in the
>details, this makes Med_history_type a repeating field still in 1NF
>
>I tried this and still think that Med_history_type is repeating field
>
>UNF 1NF
>PatientID (PK) PatientID (PK)
> " "
> " "
>Med_history_type
>Med_history_reason
>Med_history_date PatientID (PK)
>Med_history_results Med_history_type (CK)
>Med_history_notes Med_history_date (CK)
>Med_current_type Med_history_reason
>Med_current_notes Med_history_results
> Med_history_notes
>
>
> PatientID (PK)
> Med_current_type (CK)
> Med_current_notes

If PK=primary key and CK=candidate key then your indication of the keys is probably not correct. But perhaps you mean that the PK and CK attributes together form the primary key for the 1NF relation in question? That would make more sense.

Anyway, the Med_history_type field does not define a repeating group because it is not independent of the other fields. For example if you would split it off into anoter separate table (with PatientID to keep the link) then you will probably loose information.

Hope this helps,

• Jan Hidders