If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Database design problem
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". |
#2
|
|||
|
|||
Database design problem
On Mon, 8 Jun 2009 13:32:25 -0700, Dorian wrote:
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) ); |
Thread Tools | |
Display Modes | |
|
|