/* accession */
create table accession (
	accession_number     char(10)     not null,
	agent_id             int          not null,
	accession_date       datetime     null,
	dead_date            datetime     null,
	dead_flag            tinyint      not null,
	material_type        varchar(20)  null,
	plant_status         char(1)      null,
	data_quality         tinyint      not null,
	material_source      varchar(100) null,
	collector_number     char(10)     null,
	collection_date      varchar(60)  null,
	begin_date           julianday    null,
	end_date             julianday    null,
	accession_notes      varchar(255) null,
	date_entered         datetime     null,
	last_change_date     datetime     null,
	last_change_agent_id int          null,
	collector_id         int          not null,
	lexicon_id           int          not null,
	requested_by         int          not null,
	system_use           varchar(10)  null,
	fhp_lexicon_id       int          not null,
	mhp_lexicon_id       int          not null,
	aff_lexicon_id       int          not null,
	hybrid_flag          tinyint      not null,
	cbd_restrict_flag    boolean      not null,
	more_notes_flag      tinyint      not null
)
go

sp_bindefault zero_default, 'accession.dead_flag'
go

sp_bindefault zero_default, 'accession.agent_id'
go

sp_bindefault true_default, 'accession.data_quality'
go

sp_bindefault zero_default, 'accession.last_change_agent_id'
go

sp_bindefault date_default, 'accession.date_entered'
go

sp_bindefault date_default, 'accession.last_change_date'
go

sp_bindefault zero_default, 'accession.requested_by'
go

sp_bindefault zero_default, 'accession.collector_id'
go

sp_bindefault napp_default, 'accession.fhp_lexicon_id'
go

sp_bindefault napp_default, 'accession.mhp_lexicon_id'
go

sp_bindefault napp_default, 'accession.aff_lexicon_id'
go

sp_bindefault zero_default, 'accession.hybrid_flag'
go

sp_bindefault zero_default, 'accession.cbd_restrict_flag'
go

sp_bindefault zero_default, 'accession.more_notes_flag'
go

sp_bindrule boolean_rule, 'accession.dead_flag'
go

sp_bindrule boolean_rule, 'accession.hybrid_flag'
go

sp_bindrule boolean_rule, 'accession.cbd_restrict_flag'
go

sp_bindrule boolean_rule, 'accession.more_notes_flag'
go

sp_bindrule data_quality_rule, 'accession.data_quality'
go

sp_foreignkey plant_identity, accession, accession_number
go

sp_foreignkey accession_activity, accession, accession_number
go

sp_foreignkey voucher, accession, accession_number
go

sp_foreignkey plant_location, accession, accession_number
go

sp_foreignkey accession, staff, agent_id
go

sp_foreignkey accession, lexicon, lexicon_id
go

sp_foreignkey accession, lexicon, fhp_lexicon_id
go

sp_foreignkey accession, lexicon, mhp_lexicon_id
go

sp_foreignkey accession, lexicon, aff_lexicon_id
go

sp_foreignkey accession, person, requested_by
go

sp_foreignkey accession, collector_code, collector_id
go

sp_foreignkey accession, accession_notes, accession_number
go

create unique clustered index PKaccession on accession (accession_number)
go

create index IF1accession on accession (agent_id)
go

create index IF2accession on accession (lexicon_id)
go

create index IF3accession on accession (requested_by)
go

create index IF4accession on accession (fhp_lexicon_id)
go

create index IF5accession on accession (mhp_lexicon_id)
go

create index IF6accession on accession (aff_lexicon_id)
go

create index IF7accession on accession (
	accession_number,
	lexicon_id,
	fhp_lexicon_id,
	mhp_lexicon_id,
	aff_lexicon_id
)
go

create index IF11accession on accession (collector_id)
go


