Table Design and Normalization

**Nick 'The database Guy'** · Sep 12 '06, 11:55 AM

Re: Table Design and Normalization

Hi TAD,

I don't completely understand where you are now.

The data you show in your tables looks like a report.

The data should, to my mind, look like this:

County Year Season Sex

Adams 1980 1 M
Adams 1980 1 F
Adams 1980 1 M
Adams 1981 2 M

And a record is made for each deer that gets killed.

You would also have a season table that is linked to the table above on
Season, you should enforce referential integrity. You should make
SeasonID an autonumber field and the primary key.

SeasonID ¦ SeasonDesc
1 Crossbow
2 Gun
3 Longbox

and so on.

Good luck

Nick
Takeadoe wrote:

Dear NGs,
>
I recently downloaded and read a bunch of material on normalizing your
data and db design. Things aren't crystal clear yet! Part of the
problem is that nearly every thing I read used the same customer
invoice data as an example. I'm dealing with deer harvest data that
will never need updating (unlike customer data!). One nagging question
that I have deals with the 1NF and non-repeating groups. At least to
me it seems that you have two choices - either repeat groups across
records or transpose the data. Let me explain. This is a sample of my
data. When a deer is harvested by a hunter there are 4 pieces of
information I collect:
>
Sex/age of deer (Male, Female, Button)
County of harvest (Adams, Allen, Ashland...)
Hunting season (Longbow, Crossbow, Gun, SWML, and a few others)
Year
>
The raw data are summarized each year and combined with data from
previous years into a table that looks like the following:
>
TABLE A
>
Male Female Button
>
Adams 1980 Crossbow 40 100 67
Adams 1981 Gun 45 110 87
Allen 1980 Crossbow 50 700 670
>
Ignoring for a moment all that is wrong with it, my immediate question
is, should the "Male", "Female", and "Button" fields be transposed to
include a SexAge and "Value" field? IOW should the above data look like
this instead:
>
TABLE B
>
County Year Season SexAge Deer
>
Adams 1980 Crossbow M 40
Adams 1980 Crossbow F 100
Adams 1980 Crossbow B 67
Adams 1981 Gun M 45
>

From where I stand, there is at least 1 reason to set it up like TABLE

B - I'm always in need of total harvest (M+F+B). It would be much
easier to get total harvest for a county, season, and year with Table
B. So, how does this relate to "repeating groups" and first normal
form - SexAge is now repeating across records. I guess the solution
would now be separate tables!

Any and all feedback is greatly appreciated.

**Takeadoe** · Sep 12 '06, 06:25 PM

Re: Table Design and Normalization

Hey Nick - You're right on the money with your notion of what the data
should look like - in raw form. The data that I am referring to I
inherited from the folks before myself. They are not the original raw
data, but rather summaries, by county, season, year, and sex. But the
fact remains, I still use them quite a bit. Folks are always asking
about shifts in harvest among the seasons over the years and how the
composition of the harvest (antlered deer vs antlerless) has changed
and can be expected to change over time. I actually have an
11,000-record table (88Counties*25y ears*5seasons) that I'm trying to
"normalize" and get to 3NF. I'd be happy to put it on our ftp site if
you'd like to have a look at it.

Mike

Nick 'The database Guy' wrote:

Hi TAD,
>
I don't completely understand where you are now.
>
The data you show in your tables looks like a report.
>
The data should, to my mind, look like this:
>
County Year Season Sex

Adams 1980 1 M
Adams 1980 1 F
Adams 1980 1 M
Adams 1981 2 M

>
And a record is made for each deer that gets killed.
>
You would also have a season table that is linked to the table above on
Season, you should enforce referential integrity. You should make
SeasonID an autonumber field and the primary key.
>
SeasonID ¦ SeasonDesc
1 Crossbow
2 Gun
3 Longbox
>
and so on.
>
Good luck
>
Nick
Takeadoe wrote:

Dear NGs,

I recently downloaded and read a bunch of material on normalizing your
data and db design. Things aren't crystal clear yet! Part of the
problem is that nearly every thing I read used the same customer
invoice data as an example. I'm dealing with deer harvest data that
will never need updating (unlike customer data!). One nagging question
that I have deals with the 1NF and non-repeating groups. At least to
me it seems that you have two choices - either repeat groups across
records or transpose the data. Let me explain. This is a sample of my
data. When a deer is harvested by a hunter there are 4 pieces of
information I collect:

Sex/age of deer (Male, Female, Button)
County of harvest (Adams, Allen, Ashland...)
Hunting season (Longbow, Crossbow, Gun, SWML, and a few others)
Year

The raw data are summarized each year and combined with data from
previous years into a table that looks like the following:

TABLE A

Male Female Button

Adams 1980 Crossbow 40 100 67
Adams 1981 Gun 45 110 87
Allen 1980 Crossbow 50 700 670

Ignoring for a moment all that is wrong with it, my immediate question
is, should the "Male", "Female", and "Button" fields be transposed to
include a SexAge and "Value" field? IOW should the above data look like
this instead:

TABLE B

County Year Season SexAge Deer

Adams 1980 Crossbow M 40
Adams 1980 Crossbow F 100
Adams 1980 Crossbow B 67
Adams 1981 Gun M 45

>From where I stand, there is at least 1 reason to set it up like TABLE

B - I'm always in need of total harvest (M+F+B). It would be much
easier to get total harvest for a county, season, and year with Table
B. So, how does this relate to "repeating groups" and first normal
form - SexAge is now repeating across records. I guess the solution
would now be separate tables!

Any and all feedback is greatly appreciated.

**John Welch** · Sep 13 '06, 05:45 AM

Re: Table Design and Normalization

It seems to me that if all you have is totals for each unique combination of
county, year and season, then that's all you have, and no other way of
organizing it will do any better (than your table A) as far as being able to
query it, report it, etc. One question to think about, though, is: are you
going to be getting the raw data from now on, and if so, how to best set
that up and integrate it with your past summaries.
-John

"Takeadoe" <mtonkovich@msn .comwrote in message
news:1158086242 .836944.200570@ e63g2000cwd.goo glegroups.com.. .
Hey Nick - You're right on the money with your notion of what the data
should look like - in raw form. The data that I am referring to I
inherited from the folks before myself. They are not the original raw
data, but rather summaries, by county, season, year, and sex. But the
fact remains, I still use them quite a bit. Folks are always asking
about shifts in harvest among the seasons over the years and how the
composition of the harvest (antlered deer vs antlerless) has changed
and can be expected to change over time. I actually have an
11,000-record table (88Counties*25y ears*5seasons) that I'm trying to
"normalize" and get to 3NF. I'd be happy to put it on our ftp site if
you'd like to have a look at it.

Mike

Nick 'The database Guy' wrote:

Hi TAD,
>
I don't completely understand where you are now.
>
The data you show in your tables looks like a report.
>
The data should, to my mind, look like this:
>
County Year Season Sex

Adams 1980 1 M
Adams 1980 1 F
Adams 1980 1 M
Adams 1981 2 M

>
And a record is made for each deer that gets killed.
>
You would also have a season table that is linked to the table above on
Season, you should enforce referential integrity. You should make
SeasonID an autonumber field and the primary key.
>
SeasonID ¦ SeasonDesc
1 Crossbow
2 Gun
3 Longbox
>
and so on.
>
Good luck
>
Nick
Takeadoe wrote:

Dear NGs,

I recently downloaded and read a bunch of material on normalizing your
data and db design. Things aren't crystal clear yet! Part of the
problem is that nearly every thing I read used the same customer
invoice data as an example. I'm dealing with deer harvest data that
will never need updating (unlike customer data!). One nagging question
that I have deals with the 1NF and non-repeating groups. At least to
me it seems that you have two choices - either repeat groups across
records or transpose the data. Let me explain. This is a sample of my
data. When a deer is harvested by a hunter there are 4 pieces of
information I collect:

Sex/age of deer (Male, Female, Button)
County of harvest (Adams, Allen, Ashland...)
Hunting season (Longbow, Crossbow, Gun, SWML, and a few others)
Year

The raw data are summarized each year and combined with data from
previous years into a table that looks like the following:

TABLE A

Male Female Button

Adams 1980 Crossbow 40 100 67
Adams 1981 Gun 45 110 87
Allen 1980 Crossbow 50 700 670

Ignoring for a moment all that is wrong with it, my immediate question
is, should the "Male", "Female", and "Button" fields be transposed to
include a SexAge and "Value" field? IOW should the above data look like
this instead:

TABLE B

County Year Season SexAge Deer

Adams 1980 Crossbow M 40
Adams 1980 Crossbow F 100
Adams 1980 Crossbow B 67
Adams 1981 Gun M 45

>From where I stand, there is at least 1 reason to set it up like TABLE

B - I'm always in need of total harvest (M+F+B). It would be much
easier to get total harvest for a county, season, and year with Table
B. So, how does this relate to "repeating groups" and first normal
form - SexAge is now repeating across records. I guess the solution
would now be separate tables!

Any and all feedback is greatly appreciated.

**Takeadoe** · Sep 13 '06, 12:55 PM

Re: Table Design and Normalization

John - Good point. In fact, I have the raw data from 1995 data
forward, with the exception of a single year. Thanks for your
feedback.

Mike
John Welch (remove remove) wrote:

It seems to me that if all you have is totals for each unique combinationof
county, year and season, then that's all you have, and no other way of
organizing it will do any better (than your table A) as far as being ableto
query it, report it, etc. One question to think about, though, is: are you
going to be getting the raw data from now on, and if so, how to best set
that up and integrate it with your past summaries.
-John
>
"Takeadoe" <mtonkovich@msn .comwrote in message
news:1158086242 .836944.200570@ e63g2000cwd.goo glegroups.com.. .
Hey Nick - You're right on the money with your notion of what the data
should look like - in raw form. The data that I am referring to I
inherited from the folks before myself. They are not the original raw
data, but rather summaries, by county, season, year, and sex. But the
fact remains, I still use them quite a bit. Folks are always asking
about shifts in harvest among the seasons over the years and how the
composition of the harvest (antlered deer vs antlerless) has changed
and can be expected to change over time. I actually have an
11,000-record table (88Counties*25y ears*5seasons) that I'm trying to
"normalize" and get to 3NF. I'd be happy to put it on our ftp site if
you'd like to have a look at it.
>
Mike
>
>
Nick 'The database Guy' wrote:

Hi TAD,

I don't completely understand where you are now.

The data you show in your tables looks like a report.

The data should, to my mind, look like this:

County Year Season Sex

Adams 1980 1 M
Adams 1980 1 F
Adams 1980 1 M
Adams 1981 2 M

And a record is made for each deer that gets killed.

You would also have a season table that is linked to the table above on
Season, you should enforce referential integrity. You should make
SeasonID an autonumber field and the primary key.

SeasonID ¦ SeasonDesc
1 Crossbow
2 Gun
3 Longbox

and so on.

Good luck

Nick
Takeadoe wrote:

Dear NGs,
>
I recently downloaded and read a bunch of material on normalizing your
data and db design. Things aren't crystal clear yet! Part of the
problem is that nearly every thing I read used the same customer
invoice data as an example. I'm dealing with deer harvest data that
will never need updating (unlike customer data!). One nagging question
that I have deals with the 1NF and non-repeating groups. At least to
me it seems that you have two choices - either repeat groups across
records or transpose the data. Let me explain. This is a sample of my
data. When a deer is harvested by a hunter there are 4 pieces of
information I collect:
>
Sex/age of deer (Male, Female, Button)
County of harvest (Adams, Allen, Ashland...)
Hunting season (Longbow, Crossbow, Gun, SWML, and a few others)
Year
>
The raw data are summarized each year and combined with data from
previous years into a table that looks like the following:
>
TABLE A
>
Male Female Button
>
Adams 1980 Crossbow 40 100 67
Adams 1981 Gun 45 110 87
Allen 1980 Crossbow 50 700 670
>
Ignoring for a moment all that is wrong with it, my immediate question
is, should the "Male", "Female", and "Button" fields be transposed to
include a SexAge and "Value" field? IOW should the above data look like
this instead:
>
TABLE B
>
County Year Season SexAge Deer
>
Adams 1980 Crossbow M 40
Adams 1980 Crossbow F 100
Adams 1980 Crossbow B 67
Adams 1981 Gun M 45
>
From where I stand, there is at least 1 reason to set it up like TABLE
B - I'm always in need of total harvest (M+F+B). It would be much
easier to get total harvest for a county, season, and year with Table
B. So, how does this relate to "repeating groups" and first normal
form - SexAge is now repeating across records. I guess the solution
would now be separate tables!
>
Any and all feedback is greatly appreciated.

Table Design and Normalization

Table Design and Normalization

Comment

Comment

Comment

Comment