Database Design Strategies

1
Database Design Strategies
Database Design Strategies

• Alaska Support Office
• GIS Team

2
Relational Databases

• A relational database separates data by subject
  into different tables that are then related by
  common fields
• A relational database can ensure that your data
  is easily maintained over time

3
Relational Databases
Benefits of a Relational Database

• Eliminates duplicate information
• Eases data entry and maintenance
• Eases data viewing and querying

4
Tables

• A table is the primary structure in a relational
  database, composed of fields and records

5
Tables
Important Elements of a Table

• Table Name
• Table Category
• Data
• Reference
• Cross-Reference
• Table Description

6
Tables
Primary Pitfalls to Avoid in Tables

• Tables that contain data about more than one
  subject
• Tables that do not have a primary key

7
Fields

• A field is an attribute, or characteristic, of
  the subject of the table

8
Fields
Important Elements of a Field

• General Elements
• Field Name
• Table Name
• Field Description

9
Fields
Important Elements of a Field

• Table Elements
• Type of Key
• None, Primary, Foreign, Alternate
• Unique Value
• Required Value
• Data Type
• Boolean, Alphanumeric, Numeric, DateTime, Counter
• Valid Characters, Length, Decimal Places

10
Fields
Important Elements of a Field

• Data Entry Elements
• Field Label
• Values Entered By
• User, System
• Input Mask
• Display Format
• Default Value

11
Fields
Important Elements of a Field

• Data Validation/Domain Elements
• Range of Allowed Values
• Set of Allowed Values
• Validation Rule

12
Fields
Important Elements of a Field

• Query Elements
• Comparisons Allowed
• , !, gt, gt, lt, lt
• Operations Allowed
• Addition, Subtraction, Multiplication, Division

13
Fields
Primary Pitfalls to Avoid in Fields

• Fields containing multiple values
• Repeating data values
• Fields containing calculated values

14
Relationships

• A relationship is a connection between two tables
• A relationship can eliminate duplicate or
  redundant data

15
Relationships
Important Elements of a Relationship

• Parent Table Name
• Child Table Name
• Relationship Type
• One-to-One (11)
• One-to-Many (1M)
• Many-to-Many (MM)

16
Relationships
Primary Pitfalls to Avoid in Relationships

• Not using relationships or creating all fields
  in a single table that should be broken into two
  or more related tables

17
Relationships
Relational Data Red Flag

• Finding one-to-many relationships in your data
  should signify to you that a relational database
  is the best choice for your data!
• In this case, a flat file will not accurately
  model your data.

18
Normalization

• Normalization is the process of converting data
  from a flat file format into a proper relational
  database
• Normalization separates the fields from a large
  table into multiple, smaller, related tables by
  removing all unnecessary or duplicate fields
• Normalization ensures that each table represents
  only one subject

19
Normalization
Signs of a Non-Normalized Table

• All data is stored in a single table
• A single record contains information on more than
  one subject
• A single record contains repeating fields
• Multiple records contain the same values for a
  group of fields

20
Normalization

• 1NF Eliminate repeating groups

21
Normalization
Signs of a Database in First Normal Form

• Each table is identified with a primary key
• No table contains repeating values

22
Normalization

• 1NF Eliminate repeating groups
• 2NF Eliminate redundant data

23
Normalization
Signs of a Database in Second Normal Form

• No table contains redundant data or groups of
  repeated values for multiple records

24
Normalization

• 1NF Eliminate repeating groups
• 2NF Eliminate redundant data
• 3NF Eliminate columns not dependent on the key

25
Normalization
Signs of a Database in Third Normal Form

• Each table contains only fields that are
  dependent on the primary key or directly related
  to the subject of the table

26
Database Design Standards

• Database standards allow for the development of
  consistent, well-designed databases
• Important for sharing data among multiple users
• Important for merging data at a regional level
• A user familiar with the standards can open any
  database developed with those standards and have
  a good understanding of the tables

27
Database Design Standards
Advantages of a Well-Designed Database

• You can easily modify and maintain the structure
  of your tables and fields
• You can easily modify the data in your database
• You can easily retrieve and compare information
  from your database
• You can easily develop data entry windows and
  more robust applications for your database

28
Naming Standards
A good table or field name...

• has a unique descriptive name that is meaningful
  to the entire organization
• accurately, clearly, and unambiguously identifies
  the table subject or field characteristic
• has the minimum number of words necessary to
  convey the table subject or field meaning
• does not contain unknown acronyms or abbreviations

29
Naming Standards
A good table or field name...

• does not contains words that describe the
  physical characteristics, such as file,
  record, data, or table
• does not identify more than one table subject or
  field characteristic
• uses the singular form of the name

30
Naming Standards
Avoid Spaces
Poor Choices
Better Choices

• Sample Events
• Sample ID
• site id

• sample_event
• sample_id
• site_id

31
Naming Standards
Avoid Special Characters
Poor Choices
Better Choices

• Site/Locations
• phone
• MossCover
• Rain?
• project

• site_location
• phone_num
• moss_cover_percent
• is_raining
• project_cost

32
Naming Standards
Separate Words
Poor Choices
Better Choices

• birddetections
• CLOUDCOVER
• dwarfsaplingheight

• bird_detection
• cloud_cover
• dwarf_sapling_height_ft

33
Naming Standards
Use Consistent Case
Poor Choices
Better Choices

• DIGITAL_PHOTO
• SampleStartTime
• WindSpeed

• digital_photo
• sample_start_time
• wind_speed_mph

34
Naming Standards
Singularize Names
Poor Choices
Better Choices

• life_stages
• NumBirds
• Observer_Names

• life_stage
• bird_count
• observer_name

35
Naming Standards
Avoid Unknown Abbreviations
Poor Choices
Better Choices

• smptrnid
• WtrCod
• col_for_cur

• sample_transect_id
• water_code
• is_collected_for_curation

36
Naming Standards
Limit Length
Poor Choices
Better Choices

• water_quality_evaluation_code
• DescriptionOfGeomorphicDisturbance
• smptrnid

• h2o_quality_eval_code
• geomorphic_disturb_desc
• sample_transect_id

37
Naming Standards
Use Specific Names
Poor Choices
Better Choices

• AmphibianSize
• tree_size
• Tree_Name

• amphibian_length_in
• tree_height_m
• tree_species_name -or- tree_common_name

38
Naming Standards
Ensure Single Values
Poor Choices
Better Choices

• full_name
• CityStateZip
• observers_initials

• first_name, last_name
• city_name, state_code, zip_code
• observer_initials

39
Naming Standards
Avoid Calculations
Poor Choices
Better Choices

• total_line_item_cost
• plot_area_m2
• event_duration_time

• item_qty, unit_cost
• plot_width_m, plot_height_m
• event_start_time, event_end_time

40
Naming Standards
Avoid Reserved Words
Poor Choices

• avg
• count
• currency
• date
• desc
• exists

• group
• index
• key
• max
• memo
• min

• name
• number
• order
• percent
• property
• section

• set
• sum
• time
• type
• value
• year

41
Naming a Table
What category does this table fall into?

• Data table prefix name with tbl_
• Reference table prefix name with ref_
• Cross-reference table prefix name with xref_

What noun or short phrase summarizes the contents
of this table?

• This should adhere to the naming standards

42
Naming a Table
Good Choices

• tbl_site
• tbl_sample_event
• ref_park
• ref_precipitation
• xref_sample_observer

43
Naming a Field
What noun or short phrase summarizes the contents
of this field?

• This should adhere to the naming standards

What category describes the type of data in this
field?
What unit of measure, if any, is required for
this field?
44
Naming a Field
id
Data Types
Examples

• auto-number
• long
• text

• bird_detection_id
• 1, 2, 3, 25, 300, 5229
• site_id
• 1620101, 1620102, 1630410
• ROCK_CR01, ROCK_CR13
• observation_id
• 20010706, 20010724

45
Naming a Field
num
Data Types
Examples

• integer
• text

• gps_unit_num
• 0001, 0002, 0008, 0230
• telephone_num
• 9072993500, 9072221010
• trail_num
• DT005, DT062, DT455

46
Naming a Field
name, type
Data Types
Examples

• text

• water_body_name
• Pillin Lake, Marlo Creek
• species_name
• boreal toad, wood frog
• gps_type
• PLGR, Garmond III

47
Naming a Field
desc, notes, comments
Data Types
Examples

• text
• memo

• site_desc
• sample_event_notes
• observation_comments

48
Naming a Field
code
Data Types
Examples

• integer
• text

• park_code
• ANIA, DENA, KATM, WRST
• cloud_cover_code
• lt10, 10-49, 50-89, gt90
• noise_code
• None, Light, Moderate, Prohibitive

49
Naming a Field
date
Data Types
Examples

• datetime

• sample_event_date
• 06/01/2001, 08/15/2001
• recording_date
• 09/05/2001 103429 AM

50
Naming a Field
time
Data Types
Examples

• datetime

• start_time
• 082203 AM, 165524

51
Naming a Field
amt, cost
Data Types
Examples

• currency

• purchase_cost
• 95.99, 450, 5020, 8000.5

52
Naming a Field
qty
Data Types
Examples

• integer

• purchase_qty
• 1, 10, 228, 2000

53
Naming a Field
count
Data Types
Examples

• integer
• long

• bird_count
• 0, 10, 25, 530

54
Naming a Field
percent
Data Types
Examples

• any numeric

• litter_cover_percent
• 2, 55, 62.5, 80
• moss_cover_percent
• .2, .55, .625, .8

55
Naming a Field
height, width, length, depth, girth
Data Types
Examples

• any numeric

• amphibian_length_cm
• 10, 22.5
• tree_height_ft
• 3, 45
• trail_length_km
• 2.2, 225

56
Naming a Field
weight
Data Types
Examples

• any numeric

• sample_weight_g
• 850, 5300
• sample_weight_kg
• .85, 5.3
• artifact_weight_lb
• .5, 11

57
Naming a Field
temp
Data Types
Examples

• any numeric

• water_temp_degf
• 45, 56.5
• air_temp_degc
• 0, 32

58
Naming a Field
is, are, has (prefix, not suffix)
Data Types
Examples

• yes/no

• are_deformities_present
• yes, no
• is_snowing
• -1, 0
• has_litter_cover_gt_20_percent
• true, false

59
Naming a Field
What noun or short phrase summarizes the contents
of this field?

• This should adhere to the naming standards

What category describes the type of data in this
field?
What unit of measure, if any, is required for
this field?
60
Naming a Field
unit of measure distance
Data Types
Examples

• any numeric

• tree_girth_in
• river_depth_ft
• insect_length_cm
• elevation_height_m
• trail_length_km

61
Naming a Field
unit of measure weight
Data Types
Examples

• any numeric

• insect_weight_g
• amphibian_weight_kg
• artifact_weight_lb

62
Naming a Field
unit of measure temperature
Data Types
Examples

• any numeric

• water_temp_degf
• air_temp_degc

63
Naming a Field
unit of measure radial degrees
Data Types
Examples

• any numeric

• slope_deg
• aspect_deg

64
Naming a Field
unit of measure other
Data Types
Examples

• any numeric

• wind_speed_mph
• river_volume_cfs

65
Defining a Field

• Write a field definition
• Define a validation rule
• Define a label
• Define a input mask
• Define a display format

66
Design Strategies Recap

• Standards add value to your data
• Develop naming standards
• Formatting conventions
• Naming conventions