Table Setup Help Needed

[Takeadoe]

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

While I ultimately figured out how to do it with IIF and some year
criteria (which actually went pretty well), it was suggested that I
restructure my data table.

It was suggested that I restructure my table to look like the
following:

1982 Athens VarA 900
1982 Athens VarB 50
1982 Athens VarC 11.7

This got me thinking about table design a bit more and I was hoping I
could get some general guidance about setting up tables. Needless to
say, I'm new to Access. I've used SAS for years and I guess I never
really gave my tables that much thought simply because I knew how to
manipulate the data so well.

Anyhow, the following Harvest Table is one of many tables that I built
in Access.

Year County RegulationType District Season Bucks (m) Does (f)
1995 Athens 19 3 Crossbow 45
55

Season can take 1 of 5 values, District 1 of 5, RegulationType 1 of 9,
County n=88 and year can range from 1977 on

Another table would look like the following:

Year County District DVAs(deer-car crashes) RegisteredVehicles

1995 Athens 1 389 560,897
Finally, another table might be:

Year County District PermitType1 PermitType2
So, the first question is, do I keep all of these tables separate or
somehow combine them into one. There are a few instances where I will
bring all of these data together, but most often, I will simply be
looking at these data over time and computing changes from one year to
the next. There are times where I may want to group counties into 1 of
5 districts or other zones and look at changes in accidents, harvest,
or complaints from one year to the next.

Perhaps I should keep the tables separate and just restructure them to
facilitate my most common analyses. For instance, my harvest table
might look something like the following after restructuring.

County Year Sex Season District RegulationType
If I wanted to combine the harvest and deer-car crash data the
resulting table might look like... a mess! I can't see how these two
tables could be combined without a lot of missing values.

Any how, if you can follow this, I would really appreciate any guidance
you wish to share with me.

Let me say in closing that 9 of 10 queries that I will run against
these data will be computing annual changes and plotting these over
time.

Thanks in Advance...
Mike

[Smartin]

Takeadoe wrote:

Dear NG,

Hi Mike, please see responses inline.

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

While I ultimately figured out how to do it with IIF and some year
criteria (which actually went pretty well), it was suggested that I
restructure my data table.

It was suggested that I restructure my table to look like the
following:

1982 Athens VarA 900
1982 Athens VarB 50
1982 Athens VarC 11.7

This got me thinking about table design a bit more and I was hoping I
could get some general guidance about setting up tables. Needless to
say, I'm new to Access. I've used SAS for years and I guess I never
really gave my tables that much thought simply because I knew how to
manipulate the data so well.

Anyhow, the following Harvest Table is one of many tables that I built
in Access.

Year County RegulationType District Season Bucks (m) Does (f)
1995 Athens 19 3 Crossbow 45
55

Season can take 1 of 5 values, District 1 of 5, RegulationType 1 of 9,
County n=88 and year can range from 1977 on

Another table would look like the following:

Year County District DVAs(deer-car crashes) RegisteredVehicles

1995 Athens 1 389 560,897
Finally, another table might be:

Year County District PermitType1 PermitType2

I think you're on the right track. You have modeled three entities:
Harvests, Crashes, and Permits. There's some room for improvement I
suspect, read on...

>

So, the first question is, do I keep all of these tables separate or
somehow combine them into one. There are a few instances where I will
bring all of these data together, but most often, I will simply be
looking at these data over time and computing changes from one year to
the next. There are times where I may want to group counties into 1 of
5 districts or other zones and look at changes in accidents, harvest,
or complaints from one year to the next.

Since you have so many entities to model, separate tables are the way to
go. You mention counties Vs. districts. This is a good lead: In a couple
of your tables you have county and district. Do counties belong to
districts? If yes, then district is dependent on county. There's no need
to store this dependent information twice in a table in RDB design. Keep
it in a separate table.

Perhaps I should keep the tables separate and just restructure them to
facilitate my most common analyses. For instance, my harvest table
might look something like the following after restructuring.

County Year Sex Season District RegulationType

I doubt you should structure your tables based on analysis needs. You
can join related elements using queries to get at the data you want.
Unless you are processing data warehouse volumes (which is unlikely),
carefully setting up your tables using good normalization practice will
serve you well for your current and future needs.

>

If I wanted to combine the harvest and deer-car crash data the
resulting table might look like... a mess! I can't see how these two
tables could be combined without a lot of missing values.

Why not? As you proposed above, these two tables have common elements
(year, county/district). You can join on these commonalities and, if
desired, summarize by the specifics. RDB design makes this relatively
easy. A "flat" table design with everything in one row does not.

>
Any how, if you can follow this, I would really appreciate any guidance
you wish to share with me.

Let me say in closing that 9 of 10 queries that I will run against
these data will be computing annual changes and plotting these over
time.

I think you are on the right path Mike. I also think you would benefit
from taking a couple hours to study on "database normalization" (Google
that, with the quotes) to familiarize yourself with the concepts --
although I do believe you are well on the way to embracing this on your own.

>
Thanks in Advance...
Mike

Good luck

--
Smartin

[Takeadoe]

Smartin,

Thank you very much for your time and assistance. I have just printed
40 pages worth of articles on database normalization and will dig in
tonight (bed time reading!).

Thanks again. I will do my best to PayForward!

Mike
Smartin wrote:

Takeadoe wrote:

Dear NG,

Hi Mike, please see responses inline.

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

While I ultimately figured out how to do it with IIF and some year
criteria (which actually went pretty well), it was suggested that I
restructure my data table.

It was suggested that I restructure my table to look like the
following:

1982 Athens VarA 900
1982 Athens VarB 50
1982 Athens VarC 11.7

This got me thinking about table design a bit more and I was hoping I
could get some general guidance about setting up tables. Needless to
say, I'm new to Access. I've used SAS for years and I guess I never
really gave my tables that much thought simply because I knew how to
manipulate the data so well.

Anyhow, the following Harvest Table is one of many tables that I built
in Access.

Year County RegulationType District Season Bucks (m) Does (f)
1995 Athens 19 3 Crossbow 45
55

Season can take 1 of 5 values, District 1 of 5, RegulationType 1 of 9,
County n=88 and year can range from 1977 on

Another table would look like the following:

Year County District DVAs(deer-car crashes) RegisteredVehicles

1995 Athens 1 389 560,897
Finally, another table might be:

Year County District PermitType1 PermitType2

I think you're on the right track. You have modeled three entities:
Harvests, Crashes, and Permits. There's some room for improvement I
suspect, read on...

So, the first question is, do I keep all of these tables separate or
somehow combine them into one. There are a few instances where I will
bring all of these data together, but most often, I will simply be
looking at these data over time and computing changes from one year to
the next. There are times where I may want to group counties into 1 of
5 districts or other zones and look at changes in accidents, harvest,
or complaints from one year to the next.

Since you have so many entities to model, separate tables are the way to
go. You mention counties Vs. districts. This is a good lead: In a couple
of your tables you have county and district. Do counties belong to
districts? If yes, then district is dependent on county. There's no need
to store this dependent information twice in a table in RDB design. Keep
it in a separate table.

Perhaps I should keep the tables separate and just restructure them to
facilitate my most common analyses. For instance, my harvest table
might look something like the following after restructuring.

County Year Sex Season District RegulationType

I doubt you should structure your tables based on analysis needs. You
can join related elements using queries to get at the data you want.
Unless you are processing data warehouse volumes (which is unlikely),
carefully setting up your tables using good normalization practice will
serve you well for your current and future needs.

If I wanted to combine the harvest and deer-car crash data the
resulting table might look like... a mess! I can't see how these two
tables could be combined without a lot of missing values.

Why not? As you proposed above, these two tables have common elements
(year, county/district). You can join on these commonalities and, if
desired, summarize by the specifics. RDB design makes this relatively
easy. A "flat" table design with everything in one row does not.

Any how, if you can follow this, I would really appreciate any guidance
you wish to share with me.

Let me say in closing that 9 of 10 queries that I will run against
these data will be computing annual changes and plotting these over
time.

I think you are on the right path Mike. I also think you would benefit
from taking a couple hours to study on "database normalization" (Google
that, with the quotes) to familiarize yourself with the concepts --
although I do believe you are well on the way to embracing this on your own.

Thanks in Advance...
Mike

Good luck

--
Smartin