Organizing data into tables in Access
I have a database that consists of several tables:
Senators: senatorid, senatorLname, SenatorFName, SenateDistrict, CityofResidence, party
Senatorid is pk field
Representatives: repid, RepLname, RepFname, HouseDistrict, etc, (same as senator table.)
repid is pk
Cities: cityID, City
cityid is pk
Counties: countyID (PK field), County
ZipCodes: ZipID, (PK field), zipcode
AllPolitical: AllpoliticalID (PK field), CityID, ZipID, RepID, SenatorID,
countyID
I have created relationships between tables on my PK and FK fields.
I am something of a newbie and terribly confused as to how to divide these tables up. The problem is as I'm sure you know, senate and house district lines don't follow city, county or zip code lines. A zip code can span more than one county and or city, senate district or house district. A house district contains many zips, cities and counties but 2 representatives may both represent within the same zip code. If a zip
code was my one most granular unit it would be easier.
Any suggestions would be greatly appreciated.
Thanks,
Karen
Senators: senatorid, senatorLname, SenatorFName, SenateDistrict, CityofResidence, party
Senatorid is pk field
Representatives: repid, RepLname, RepFname, HouseDistrict, etc, (same as senator table.)
repid is pk
Cities: cityID, City
cityid is pk
Counties: countyID (PK field), County
ZipCodes: ZipID, (PK field), zipcode
AllPolitical: AllpoliticalID (PK field), CityID, ZipID, RepID, SenatorID,
countyID
I have created relationships between tables on my PK and FK fields.
I am something of a newbie and terribly confused as to how to divide these tables up. The problem is as I'm sure you know, senate and house district lines don't follow city, county or zip code lines. A zip code can span more than one county and or city, senate district or house district. A house district contains many zips, cities and counties but 2 representatives may both represent within the same zip code. If a zip
code was my one most granular unit it would be easier.
Any suggestions would be greatly appreciated.
Thanks,
Karen
Karen,
Also, will your db cover more than 1 legislative term? If so, you current structure is not really able to cope with history.
Regards,
Patrick
Also, will your db cover more than 1 legislative term? If so, you current structure is not really able to cope with history.
Regards,
Patrick
Hi Karen,
I agree with Patrick. One table is better than two. Here's a link that might give you a few ideas:
http://www.functionx.com/access/databases/ussenate1.htm
Flyster
I agree with Patrick. One table is better than two. Here's a link that might give you a few ideas:
http://www.functionx.com/access/databases/ussenate1.htm
Flyster
ASKER
But my AllPolitical table is what ties everything together. How else can I do that?
Karen,
> But my AllPolitical table is what ties everything together. How else can I do that?
With queries. That is the whole point of relational databases.
Regards,
Patrick
> But my AllPolitical table is what ties everything together. How else can I do that?
With queries. That is the whole point of relational databases.
Regards,
Patrick
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Patrick,
Thanks so much. I'm going to implement your structure and see how it goes! I'm going to have to do alot of queries to make the tables so it could take a while!
Karen
Thanks so much. I'm going to implement your structure and see how it goes! I'm going to have to do alot of queries to make the tables so it could take a while!
Karen
Karen,
Just to confuse you some more.
You may want to ask your Secretary of State to send you a file of Town polling places and addresses.
The file that I have from Connecticut has the following information:
ID
fldTown
fldPollingPlaceName
fldAddress1
fldAddress2
fldZipCode
fldVotingDistrict
fldPrecinct
fldCongressDistrict
fldSenateDistrict
fldAssemblyDistrict
First record is :
2
Andover
Andover Elementary School
37 School Road
Andover
6232
1
0
2
19
55
Voting district and precinct are town designated fields.
fldCongressDistrict, fldSenateDistrict and fldAssemblyDistrict
are State designated Fields.
Since districts can span towns you would be able to then show the town(s) served by the Senator or Representative.
Don
Just to confuse you some more.
You may want to ask your Secretary of State to send you a file of Town polling places and addresses.
The file that I have from Connecticut has the following information:
ID
fldTown
fldPollingPlaceName
fldAddress1
fldAddress2
fldZipCode
fldVotingDistrict
fldPrecinct
fldCongressDistrict
fldSenateDistrict
fldAssemblyDistrict
First record is :
2
Andover
Andover Elementary School
37 School Road
Andover
6232
1
0
2
19
55
Voting district and precinct are town designated fields.
fldCongressDistrict, fldSenateDistrict and fldAssemblyDistrict
are State designated Fields.
Since districts can span towns you would be able to then show the town(s) served by the Senator or Representative.
Don
ASKER
Patrick,
I have implemented the design you suggested. It took quite a long time to do as you can imagine. I had such ratty tables it was unreal. Anyway it looks real good. The relationship view is very elegant and spare compared to the miasma I had going before. One thing I did differently was in the ZipDistrict table, instead of one district ID I created a HousedistrictID and SenateDistrictID. Both fields are related to the districtID field from the district table. The reason is each of those zipdistrict records will have a house and a senate district and otherwise it would mean making 2 separate records out of what would have been one. I know that's not normalized. I can change it if you think it should be. The other thing (I feel like I'm asking for a whole access class here on the site - maybe I should create another topic for this but I really want your input.) - I've never had much success with access forms based on many different tables. What's the best way to go at it?
Thanks so much for your great advice and for designing my table structure!!
Karen
I have implemented the design you suggested. It took quite a long time to do as you can imagine. I had such ratty tables it was unreal. Anyway it looks real good. The relationship view is very elegant and spare compared to the miasma I had going before. One thing I did differently was in the ZipDistrict table, instead of one district ID I created a HousedistrictID and SenateDistrictID. Both fields are related to the districtID field from the district table. The reason is each of those zipdistrict records will have a house and a senate district and otherwise it would mean making 2 separate records out of what would have been one. I know that's not normalized. I can change it if you think it should be. The other thing (I feel like I'm asking for a whole access class here on the site - maybe I should create another topic for this but I really want your input.) - I've never had much success with access forms based on many different tables. What's the best way to go at it?
Thanks so much for your great advice and for designing my table structure!!
Karen
Karen,
> I have implemented the design you suggested. It took quite a long time to do as you can imagine.
I certainly can imagine. I do think, though, that implementing a good design is worth the upfront time.
> I had such ratty tables it was unreal. Anyway it looks real good. The relationship view is very elegant
> and spare compared to the miasma I had going before.
Glad I was able to help you clean it up :)
> One thing I did differently was in the ZipDistrict table, instead of one district ID I created a
> HousedistrictID and SenateDistrictID. Both fields are related to the districtID field from the district table.
> The reason is each of those zipdistrict records will have a house and a senate district and otherwise it
> would mean making 2 separate records out of what would have been one. I know that's not
> normalized. I can change it if you think it should be.
I do not think that approach, and here is why: district boundaries may leave a single ZIP code in >1 House
and/or Senate district.
> The other thing (I feel like I'm asking for a whole access class here on the site - maybe I should
> create another topic for this but I really want your input.) - I've never had much success with access
> forms based on many different tables. What's the best way to go at it?
I think nesting subforms is probably going to be your most expedient approach.
> Thanks so much for your great advice and for designing my table structure!!
You're welcome :)
Patrick
> I have implemented the design you suggested. It took quite a long time to do as you can imagine.
I certainly can imagine. I do think, though, that implementing a good design is worth the upfront time.
> I had such ratty tables it was unreal. Anyway it looks real good. The relationship view is very elegant
> and spare compared to the miasma I had going before.
Glad I was able to help you clean it up :)
> One thing I did differently was in the ZipDistrict table, instead of one district ID I created a
> HousedistrictID and SenateDistrictID. Both fields are related to the districtID field from the district table.
> The reason is each of those zipdistrict records will have a house and a senate district and otherwise it
> would mean making 2 separate records out of what would have been one. I know that's not
> normalized. I can change it if you think it should be.
I do not think that approach, and here is why: district boundaries may leave a single ZIP code in >1 House
and/or Senate district.
> The other thing (I feel like I'm asking for a whole access class here on the site - maybe I should
> create another topic for this but I really want your input.) - I've never had much success with access
> forms based on many different tables. What's the best way to go at it?
I think nesting subforms is probably going to be your most expedient approach.
> Thanks so much for your great advice and for designing my table structure!!
You're welcome :)
Patrick
I left a followup in the other Q you just posted.
I think you need to revisit the schema. In particular:
1) Having separate tables for Senate and House is a bad idea. One table, with a field that indicates House or Senate
is, IMHO, a much better approach
2) I think you need to avoid using city/county/ZIP. As you note, they just do not line up neatly
3) The AllPolitical table is a bad, bad idea. It is essentially derived data, and should be served up by a query and
not saved in a table
Regards,
Patrick