Transitive dependencies

Firstly, I am a new poster here and would like to say hello to everyone.

I have a transitive dependency problem that I am positive would benefit from the groups insight.

I have been tasked with remodeling a design (to improve integrity) that supports a survey. I have simplified the design for the sake of clarity.

The gist of the survey is:
Agencies complete award data about moneys in categories awarded to institutes.

Business Rules:
Awards consist of 9 categories (two are shown for simplicity - research, support) that are amounts in dollars.
When the award is saved the data are validated against previous years to check for variance errors.
Each category amount can have a maximum of one error associated with it at any time.
A category error is defined as a type of variance from previous years data. Each category error belongs to a separate domain. (i.e. A variance for one category is not applicable to another category). Each category error has a complicated description.

The award relation is currently modeled as followed (<primary key>):

award ( <institute, year>, research, research_err, support, support_err... ) Clearly not in 3NF.

I see the award relation functional dependencies as follows:

FD1 (institute, year) -> research, research_err, support, support_err FD2 research -> research_err
FD3 support -> support_err

Giving the following transitive dependencies:

TD1 (institute, year) -> research -> research_err TD2 (institute, year) -> support -> support_err

The real survey has 9 such categories that have changed very little since 1965! How would one normalize the award relation to remove the transitive dependencies? I have three proposed designs:

Solution 1:
award (<institute, year>, research_amount, support_amount) (1 tuple per award)
award_error (<institute, year, error>) FK - award, error error(<error>, description)
All the errors are lumped into a single domain and its difficult to find out what category each error refers to.

Solution 2:
Decompose award category attributes (research, support) into a single category entity:

award_category (<institute, year, category> amount) (n tuples per award) category_error(<institute, year, category, error>) FK - award_category, error
error(<category, error>), description)

Solution 3:
Decompose each award category attribute (research, support) into separate entities:

research(<institute, year, research>, amount, error) FK - research_error support(<institute, year, category>, amount, error) FK - support_error research_error(<error>, description)
support_error(<error>, description)

Remember the categories have changed twice since 1965 so adding or removing categories is not that painful. I am leaning towards Solution 3 because its simple and I can construct the non normalized award relation from it quite simply (the data is used by a web based app layer and they will want to see an award, with error information as 1 tuple rather than n tuples). I'm sure there is a better way but my brain can't see it. Does it even need normalizing further? I understand it's difficult to jump into a problem space but any suggestions and comments on the proposed designs would be gratefully received. My apologies for the verbosity of this post.

Thank you in advance,

Mark Received on Tue Oct 07 2003 - 18:03:48 CEST