redundancy in the table

[talktobatchu]

Hello
there is a table where the data related to the
number of cases of a particular disease are stored with
year, name of the county, age groups and so on as
columns. It happens to be that, year and countyname are
getting repeated each time in entering the values for
different years for the respective counties.

Table Design:
Field Name - Data Type
Entry ID - AutoNumber
Year - Number
CountyName - Text
TotalNumberOfCases - Number
AgeGroup(<15) - Number
AgeGroup(15-17) - Number
AgeGroup(18-19) - Number
AgeGroup(20-24) - Number
AgeGroup(25-29) - Number
AgeGroup(30-34) - Number
AgeGroup(>35) - Number

the table contains data by year and county wise for the
age groups available.so the year and countynames appear
manytimes in the table.anybody can help me in suggesting
a efficient table design.

thanks for any help.
regards
talktobatchu

 

[Eric Butts [MSFT]]

Hi,

Here's a suggestion:

TABLE1:
------------
CountyName - Text (Primary key)

TABLE2:
--------------
ID (Primary key)
Year - Number (composite index - unique)
CountyName - Text (foreign key) (composite index - unique)

TABLE3:
--------------
RecordNum (Primary key)
AgeGroup
Amount
ID (foreign key)

xxxxxxxxxxxxxx So the data would like the following: xxxxxxxxxxxxxxxxxxxxxxx

TABLE1:
CountyName
-----------------
Test County

TABLE2:
ID Year CountyName
------ ------- -----------------
A 1994 Test County
B 1995 Test County

TABLE3:
RecordNum AgeGroup Amount ID
-------------- ------------ ---------- -----------
1 15-17 10 A
2 15-17 20 B
3 >35 50 A


I hope this helps! If you have additional questions on this topic, please
respond back to this posting.


Regards,

Eric Butts
Microsoft Access Support
(e-mail address removed)
"Microsoft Security Announcement: Have you installed the patch for
Microsoft Security Bulletin MS03-026? If not Microsoft strongly advises
you to review the information at the following link regarding Microsoft
Security Bulletin MS03-026
<http://www.microsoft.com/security/security_bulletins/ms03-026.asp> and/or
to visit Windows Update at <http://windowsupdate.microsoft.com/> to install
the patch. Running the SCAN program from the Windows Update site will help
to insure you are current with all security patches, not just MS03-026."

This posting is provided "AS IS" with no warranties, and confers no rights




--------------------
| Content-Class: urn:content-classes:message
| From: "talktobatchu" <[email protected]>
| Sender: "talktobatchu" <[email protected]>
| Subject: redundancy in the table
| Date: Mon, 4 Oct 2004 14:12:11 -0700
| Lines: 30
| Message-ID: <[email protected]>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="iso-8859-1"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| thread-index: AcSqVtA9oT1CM4AsT0itMDwFRlVagA==
| X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
| Newsgroups: microsoft.public.access.tablesdbdesign
| Path: cpmsftngxa06.phx.gbl
| Xref: cpmsftngxa06.phx.gbl microsoft.public.access.tablesdbdesign:84595
| NNTP-Posting-Host: tk2msftngxa11.phx.gbl 10.40.1.163
| X-Tomcat-NG: microsoft.public.access.tablesdbdesign
|
| Hello
| there is a table where the data related to the
| number of cases of a particular disease are stored with
| year, name of the county, age groups and so on as
| columns. It happens to be that, year and countyname are
| getting repeated each time in entering the values for
| different years for the respective counties.
|
| Table Design:
| Field Name - Data Type
| Entry ID - AutoNumber
| Year - Number
| CountyName - Text
| TotalNumberOfCases - Number
| AgeGroup(<15) - Number
| AgeGroup(15-17) - Number
| AgeGroup(18-19) - Number
| AgeGroup(20-24) - Number
| AgeGroup(25-29) - Number
| AgeGroup(30-34) - Number
| AgeGroup(>35) - Number
|
| the table contains data by year and county wise for the
| age groups available.so the year and countynames appear
| manytimes in the table.anybody can help me in suggesting
| a efficient table design.
|
| thanks for any help.
| regards
| talktobatchu
|

 

[Tim Ferguson]

Table Design:
Field Name - Data Type
Entry ID - AutoNumber
Year - Number
CountyName - Text
TotalNumberOfCases - Number
AgeGroup(<15) - Number
AgeGroup(15-17) - Number
AgeGroup(18-19) - Number
AgeGroup(20-24) - Number
AgeGroup(25-29) - Number
AgeGroup(30-34) - Number
AgeGroup(>35) - Number

I would get rid of all these AgeGroup columns and normalise the design:

Incidences
Year
County
AgeGroup
NumberOfCases
Primary Key (Year, County, AgeGroup)
Foreign Key County references Counties
Foreign key AgeGroup references AgeGroups

In order to control the AgeGroups, you use a table that looks like

AgeGroups
GroupCode Primary Key
StartAge
TopAge

Hope that helps


Tim F

 

[talktobatchu]

hi
thanks eric for your time and help. when I had
designed the table as you have suggested,
the 'TABLE2'still has multiple entries of year and
counties. Actually my data looks as follows:

'RecordID' 'yearfield' 'countyname' 'total' 'lessthan
-15' '15-17' '18-19' '20-24' '18-19' '20-24' '25-
29' '30-34' '35 and above' these are all the columns in
my present table.

so the year and county names are being repeated when I
enter data for different years for all the respective
counties. what needs to be done for the table design.

thanks for any help
regards,
talktobatchu

-----Original Message-----
Hi,

Here's a suggestion:

TABLE1:
------------
CountyName - Text (Primary key)

TABLE2:
--------------
ID (Primary key)
Year - Number (composite index - unique)
CountyName - Text (foreign key) (composite index - unique)

TABLE3:
--------------
RecordNum (Primary key)
AgeGroup
Amount
ID (foreign key)

xxxxxxxxxxxxxx So the data would like the following: xxxxxxxxxxxxxxxxxxxxxxx

TABLE1:
CountyName
-----------------
Test County

TABLE2:
ID Year CountyName
------ ------- -----------------
A 1994 Test County
B 1995 Test County

TABLE3:
RecordNum AgeGroup Amount ID
-------------- ------------ ---------- -- ---------
1 15-17 10 A
2 15-17 20 B
3 >35 50 A


I hope this helps! If you have additional questions on this topic, please
respond back to this posting.


Regards,

Eric Butts
Microsoft Access Support
(e-mail address removed)
"Microsoft Security Announcement: Have you installed the patch for
Microsoft Security Bulletin MS03-026? If not Microsoft strongly advises
you to review the information at the following link regarding Microsoft
Security Bulletin MS03-026
<http://www.microsoft.com/security/security_bulletins/ms0 3-026.asp> and/or
to visit Windows Update at

the patch. Running the SCAN program from the Windows Update site will help
to insure you are current with all security patches, not just MS03-026."

This posting is provided "AS IS" with no warranties, and confers no rights