Simple Design/1NF question

[Guest]

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


County Year Season 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 Spencer]

From what I can see, you are correct in your analysis.

 

[Guest]

I would vote for version B. You can always use queries to extract your data.
You can achieve the transformation you want with a cross tab query.

 

[Guest]

Thank you very much!

I would vote for version B. You can always use queries to extract your data.
You can achieve the transformation you want with a cross tab query.

 

[Guest]

Thank you John. Should I interpret your response to mean - Table B is the
correct way to go?

Mike

 

[John Spencer]

Yes. Table B is the way to go.

Your life will be a lot simpler if someone wants to know by county the average
number of Deer "harvested" over the period 1980 to 1990.

Or if they want the total number "harvested" per year
or if they want the total number "harvested" per year per county

etc.

 

[Guest]

Thanks again John. I'm well on my way to making life easier!

Mike