Database design problem

[Dorian]

I've been wrestling with this design for a while and would appreciate some
input.
This is a database that handles audits of determinations made by remote
offices.

Each year, a new review series is set up to audit several sites.
Each review series targets up to 3 areas of determinations (e.g. citizenship
or income level). So for each review in the series the target areas are
predetermined.
Each individual review relates to a review series and to a site.
Each review handles the 1-3 target areas and each target area has a number
of cases (determinations about individual people). Each target area may have
different cases or the same cases.
The question is: do I have reviews related to cases and then cases related
to target areas or do I have reviews related to target areas and then target
areas related to cases?
Also, since the review series predetermines the target areas, how do I
relate the target areas to reviews. I need to keep several data items
relating to a case separated out by target area.
If it makes any difference, the users say that when conducting a review they
process by case and audit each target area for that case before moving on to
the next case.
I'm trying to normalize this but will happily denormalize if it make the
application easier to develop.

Thanks for any input.
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".

 

[Michael Gramelspacher]

I've been wrestling with this design for a while and would appreciate some
input.
This is a database that handles audits of determinations made by remote
offices.

Each year, a new review series is set up to audit several sites.
Each review series targets up to 3 areas of determinations (e.g. citizenship
or income level). So for each review in the series the target areas are
predetermined.
Each individual review relates to a review series and to a site.
Each review handles the 1-3 target areas and each target area has a number
of cases (determinations about individual people). Each target area may have
different cases or the same cases.
The question is: do I have reviews related to cases and then cases related
to target areas or do I have reviews related to target areas and then target
areas related to cases?
Also, since the review series predetermines the target areas, how do I
relate the target areas to reviews. I need to keep several data items
relating to a case separated out by target area.
If it makes any difference, the users say that when conducting a review they
process by case and audit each target area for that case before moving on to
the next case.
I'm trying to normalize this but will happily denormalize if it make the
application easier to develop.

Thanks for any input.
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".

I do not see where anyone has answered yet, so I will take a stab at it.

CREATE TABLE Cases (
Case_ID TEXT(10) NOT NULL,
CONSTRAINT PK_Cases PRIMARY KEY (Case_ID)
);
CREATE TABLE Reviews (
Review_ID TEXT(10) NOT NULL,
CONSTRAINT PK_Reviews PRIMARY KEY (Review_ID)
);
CREATE TABLE ReviewSeries (
Series_id TEXT(10) NOT NULL,
CONSTRAINT PK_ReviewSeries PRIMARY KEY (Series_id)
);
CREATE TABLE Sites (
Site_ID TEXT(10) NOT NULL,
CONSTRAINT PK_Sites PRIMARY KEY (Site_ID)
);
CREATE TABLE TargetAreas (
Area_ID TEXT(10) NOT NULL,
CONSTRAINT PK_TargetAreas PRIMARY KEY (Area_ID)
);
CREATE TABLE ReviewSites (
Review_ID TEXT(10) NOT NULL,
FOREIGN KEY (Review_ID) REFERENCES
Reviews (Review_ID),
Site_ID TEXT(10) NOT NULL,
FOREIGN KEY (Site_ID) REFERENCES
Sites (Site_ID),
CONSTRAINT PK_ReviewSites PRIMARY KEY (Review_ID, Site_ID)
);
CREATE TABLE SeriesAreas (
Series_ID TEXT(10) NOT NULL,
FOREIGN KEY (Series_ID) REFERENCES
ReviewSeries (Series_id),
Area_ID TEXT(10) NOT NULL,
FOREIGN KEY (Area_ID) REFERENCES
TargetAreas (Area_ID),
CONSTRAINT PK_SeriesAreas PRIMARY KEY (Series_ID, Area_ID)
);
CREATE TABLE SeriesAreaCases (
Series_ID TEXT(10) NOT NULL,
Area_ID TEXT(10) NOT NULL,
FOREIGN KEY (Series_ID, Area_ID) REFERENCES
SeriesAreas (Series_ID, Area_ID),
Review_ID TEXT(10) NOT NULL,
Site_ID TEXT(10) NOT NULL,
FOREIGN KEY (Review_ID, Site_ID) REFERENCES
ReviewSites (Review_ID, Site_ID),
Case_ID TEXT(10) NOT NULL,
FOREIGN KEY (Case_ID) REFERENCES
Cases (Case_ID),
CONSTRAINT PK_SeriesAreaCases
PRIMARY KEY (Series_ID, Area_ID, Review_ID, Site_ID, Case_ID)
);