Lesson 4

Design/Build GIS (Week 1)  -  Database Design

My database design for Lesson 4 consists of 3 tables, a base table and two look-up tables.  The base table (Voter_List) holds information about the individual, one look-up table (Ethnicity) holds information that defines the various ethnic groupings being captures, and one look-up table (Party) holds information that defines the various party affiliations under study.  These tables are shown below:

Voter_List

Ethnicity

Party

There is a one-to-many relationship between the Ethnicity and Party tables and the Voter_List table, such that there will be one record in the Ethnicity table for any given record in the Voter_List table, and there will be one record in the Party table for any given record in the Voter_List table, though technically a non-affiliated voter might not have a value, in this case I made the assumption that a non-party-affiliated voter would be classified as "other".

The primary keys for these tables are defined by the ArcGIS software, however, the key variables under consideration are:

• Voter ID in the Voter_List table because there needs to be a one-for-one relationship between a Voter ID number and a voter.

• EthID in the Ethnicity table because there needs to be a one-for-one relationship between the ID code and the ethnic group description.

• PartyID in the Party table because there needs to be a one-for-one relationship between the ID code and the party definition.

Null values are not allowed for any of the variables.  The only possible exception to this might be Property ID Number (PIN) where it might not be necessary to carry a PIN value for each voter.  Otherwise, all values must be present, a voter must have a Voter ID and each voter must be categorized by ethnicity and by party.

Because each voter will have their own unique data captured, no default values have been allowed.

Disk storage space has been minimized by:

• Using single precision integer values were possible.

• Minimizing the length of text variables.

• Minimizing the amount of redundant data carried in different tables.

The data collection effort was minimized by:

• Creating separate tables exclusively for ethnic and party definitions.

• Ethnic and party identification can be referenced by a single digit code instead of forcing someone to key each ethnic grouping or party affiliation for each voter.

Data entry mistakes were minimized by:

• Limiting the field sizes to the minimums required.  This forces the person entering the data to move from field to field or from one record to another once a field size has been filled avoiding the problem of not moving to the next field or record once data has been entered.  Attempting to 'overfill' a data value will result in an error tone.  This process was validated when I personally entered all the necessary data into the tables and encountered this situation a couple of times.

• By creating separate tables for ethnic and party definitions, misspellings can be avoided because one doesn't need to re-key these values for each voter each time a voter record is entered.

ArcMap can easily and efficiently query these data by simply joining the appropriate data tables using the identified keys and then selecting and displaying the appropriate fields from that query result.