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  1325 
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*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.
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.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.
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.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.
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,
...
}
Employee table has a primary key (combination of last_name and first_name).
|
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 problem is design structure. Where to begin, etc.
I see that everyone recommends The Access 97 Developer's Handbook (I
am looking for a Access 97 book), but is that going to be too advanced
in terms of getting a db off the ground? I am thinking I...
|
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 cell (primary key) and then upto 10 cells of file names of the
pictures.
The problem is that I do not know in advance how much pictures will I
have (since it depends of each item that is added)
|
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 shorten them like this
YELLOW->YEL,BLACK->BLK,WHITE->WHT...
so that the colour field would look like this YEL/BLK/RED and so on.
Please help!
|
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 areas, public areas),
grounds, bathrooms, hallways, etc. User would like to keep track of
all spaces as well as items in them, and the condition of items (ie:
beds, so he can budget when it's time to replace them). He does not
want to track...
| | |
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 Bonds, the
Prime rate, and so-forth.
We associate a security with one of those rates.
There are a set of rates for each calendar day, and the rates for that
|
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 VarB VarC etc.
1982 Athens 900 50 11.7
1983 Athens 700 40 21
|
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 equipment to use for a
customer's needs. It basically consists of a table with the following
setup:
Equipment: Option1: Option2: Option3: Option 4: ...
-------- ------- ------- -------- --------
|
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 been thinking
about different approaches to the mysql table schema that stores basic
user login information. At present, user authentication is keyed to a
table with the following columns:
TABLE: basic_user
uid - int
handle - varchar *
|
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 or
Array, correct? An example: create the object, create an array, the
stuff the object into the array. Later on, assume the object is
mutable, the object changes, but you can find it, if you have enough
state information to uniquely identify...
|
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, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
| | |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
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 Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
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 presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| | |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
|
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |
