Hacking J-Bird: Database Schema

Schema Version 1, since J-Bird 0.4.1

The following table descriptions are ordered such that all foreign keys in a table reference tables that are above it on the page.

• Table metadata - one row.




Column name	Type	Miscellaneous Information
SchemaVersion	integer	primary key




• Table families - one row for each family.




Column name	Type	Miscellaneous Information
FamNo	integer	primary key
FamScientific	VARCHAR 30	not null
FamEnglish	VARCHAR 35	not used anywhere
SortOrder	integer	not null




• Table genera - one row for each genus.




Column name	Type	Miscellaneous Information
FamNo	integer	not null, foreign key families.FamNo key
GenNo	integer	primary key
Genus	VARCHAR 25	not null
SortOrder	integer	not null
GlobalSortOrder	integer	not null




• Table synogenera - one row for each genus synonym.




Column name	Type	Miscellaneous Information
GenNo	integer	not null, foreign key genera.GenNo
Synonym	VARCHAR 25	not null, unique




• Table species - one row for each species.




Column name	Type	Miscellaneous Information
GenNo	integer	not null, foreign key genera.GenNo
SpecNo	integer	primary key
Species	VARCHAR 40	not null
SortOrder	integer	not null
GlobalSortOrder	integer	not null




• Table synospecies - one row for each species synonym.




Column name	Type	Miscellaneous Information
SpecNo	integer	not null, foreign key species.SpecNo
Synonym	VARCHAR 65	not null, unique




• Table commonlists - one row for each list of common names.




Column name	Type	Miscellaneous Information
ListNo	integer	not null, primary key
Encoding	VARCHAR 5	not null
ListName	VARCHAR 30	not null
Description	VARCHAR 100	not null
Source	VARCHAR 60	not null
Contributor	VARCHAR 60	not null
Version	VARCHAR 30	not null




• Table commonnames - one row for each species in each list of common names.




Column name	Type	Miscellaneous Information
SpecNo	integer	not null, foreign key references species.SpecNo
ListNo	integer	not null, foreign key references commonlists.ListNo
Common	VARCHAR 40	not null
		Primary key for this table is SpecNo, ListNo




• Table checklistinfo - one row for each checklist.




Column name	Type	Miscellaneous Information
ListNo	integer	primary key
ListName	VARCHAR 30	not null, unique
Description	VARCHAR 100	null
Source	VARCHAR 60	not null
Contributor	VARCHAR 60	not null
Version	VARCHAR 30	null
ParentList	integer	null, foreign key checklistinfo.ListNo




• Table checklist - one row for each species in each checklist.




Column name	Type	Miscellaneous Information
ListNo	integer	not null, foreign key checklistinfo.ListNo
SpecNo	integer	not null, foreign key species.SpecNo
		Primary key for this table is ListNo, SpecNo




• Table regions - one row for each region.




Column name	Type	Miscellaneous Information
RegNo	integer	primary key
RegName	VARCHAR 20	unique
PrefChecklist	integer	null, foreign key checklistinfo.ListNo




• Table notes - one row per note. Notes are stored as HTML documents.




Column name	Type	Miscellaneous Information
NoteNo	integer	primary key
Notes	text	not null




• Table trips - one row per trip.




Column name	Type	Miscellaneous Information
TripNo	integer	primary key
ObsDate	date	not null
Region	integer	not null, foreign key references regions.RegNo
Locality	varchar 25	not null
KeyWords	varchar 40	null
NoteNo	integer	null, foreign key references notes.NoteNo
PrefChecklist	integer	null, foreign key checklistinfo.ListNo
		Combination of region, date and locality must be unique. Enforced by code when trips are created.




• Table observers - one row per observer.




Column name	Type	Miscellaneous Information
ObsNo	integer	primary key
ObsName	varchar 20	not null, unique




• Table ticks - one row per tick.




Column name	Type	Miscellaneous Information
SpecNo	integer	not null, foreign key references species.SpecNo
TripNo	integer	not null, foreign key references trips.TripNo
Observer	integer	not null, foreign key references observers.ObsNo
BirdCount	integer	null
NoteNo	integer	null, foreign key references notes.NoteNo
		Primary key for this table SpecNo, TripNo, Observer

Change Log

• Schema version 1 beginning J-Bird 0.4.1
• Dawn of the era of schema version numbers
• Add table metadata
• Add table synospecies
• Add table synogenera
• Table checklistinfo
• Column ParentList remains unconstrained by definition.
• NULL ParentList values for any checklist other than the master checklist are set to zero on upgrade and after the database is rebuilt from comma-separated values.
• Table regions add column PrefChecklist
• Table trips add column PrefChecklist