Simple Design and 1NF question

[mtonkovich]

Dear NGs,

First, I apologize for cross posting, but I just noticed this group and
it seems much more fitting to post this question here.



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.

 

[Guest]

The simple answer is that you shouldn't have either Table A or B! For the
most part you should never have summerized data in a table at all. What
happens if you find some data that wasn't entered properly and have to make a
change? You have to remember to change two different tables which can lead to
inconsistant data.

Keep your first table as it seem to be good except that you didn't mention a
primary key field. At the minimum add an autonumber field as the PK.

From this table you can dynamically create a crosstab query that looks like
your Table A.

Table B's information can be created using a totals query as needed.

By using queries instead of storing the data in redundent tables, you can
have the information in both A and B in just a few seconds with the most
current data in your tables.

 

[mtonkovich]

Jerry - First, let me say thank you for taking time to reply. I really
appreciate it. I have table A because I inherited it! And yes, it is
a summary of raw harvest data that spans nearly 3 decades. From 1995
forward, I have the raw data that is summarized in "Table A." Prior to
that, the raw data is only available is summary format. Perhaps it
might help if I elaborate a bit on what I use this summary data for.
Just yesterday a sports writer called and wanted me to comment on the
popularity archery hunting here in Ohio. My answer was a bar chart
showing the proportion of the total (all seasons combined) harvest
taken by archers over the past 10 years. Another example - I'm often
asked about how hunter selectivity varies by weapon type (compound bow
vs crossbow in particular). Thus, I might look at proportion of the
entire compound harvest that is antlered (bucks/bucks+does+buttons)
compared to crossbows over time. Finally, I regularly take requests
from folks that simply want to see harvest trends summarized over time
say for a particular county or group of counties. So - if I'm stuck
with this table, should I make it look like Table A or B?

Mike