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. 4  1309 
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.
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*25years*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.
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" <mt********@msn.comwrote in message
news:11**********************@e63g2000cwd.googlegr oups.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*25years*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 - 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" <mt********@msn.comwrote in message
news:11**********************@e63g2000cwd.googlegr oups.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*25years*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.
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: jim |
last post by:
I have two tables that are related by keys. For instance,
Table employee {
last_name char(40) not null,
first_name char(40) not null,
department_name char(40) not null,
age int not null,
......
|
by: Aya9877 |
last post by:
Alright, I have no idea what level book I need.
I have a basic/decent background in coding. I am advanced in query
building. I have a decent understanding of Visual Basic and some VBA.
My...
|
by: E.U. |
last post by:
Hi,
I an using MS-Access in order to build a site.
I have this item that can have upto 10 pictures (might have none)
I want to design a dynamic table which has the ID of the item at the
first...
|
by: marko |
last post by:
Hi!
A have a problem. I have a table containing
ID,Brend,Model,Colour,Price.
The Colour field has colours like: YELLOW/BLACK/RED, YELLOW/RED, BLUE,
BLUE/NAVY/RED/CHARCOAL.
Now i would like to...
|
by: lorirobn |
last post by:
Hello,
I'd be curious to hear other thoughts on my database and table design.
My database is for 'space use' in a lodging facility - will hold all
spaces, like rooms (lodging rooms, dining...
|
by: PeteCresswell |
last post by:
I've got something called "Reference Rates".
The idea is that on a given day, we have various rates of return for
various entities.
e.g. Libor 3-month return, Libor 6-month return, US Treasury...
|
by: Takeadoe |
last post by:
Dear NG,
In an earlier post to the group, I was trying to find and easy way to
calculate %change estimates between years for a group of variables. My
data looks like this:
Year County VarA...
|
by: yanjie.ma |
last post by:
Hi,
I've got a two part question on table and form design (sorry for the
length but it takes a bit to explain). Our sales department uses a
look-up table to help the them select the best...
|
by: klenwell |
last post by:
I'm in the process of refactoring the php code base I've amassed over
the last few years into an object-oriented framework. I'm about to
start in on the authentication/login extension and I've...
|
by: raylopez99 |
last post by:
A quick sanity check, and I think I am correct, but just to make
sure: if you have a bunch of objects that are very much like one
another you can uniquely track them simply by using an ArrayList...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
| |
