/* garden_location */
create table garden_location (
	garden_loc_id           key_t           not null,
	loc_type                varchar(10)     not null,
	loc_name                varchar(50)     not null,
	loc_descr               varchar(255)    null,
	loc_abbrev              varchar(4)      null,
	parent_id               key_t           not null,
	loc_hier                varchar(255)    null,
	full_loc_name           varchar(255)    null,
	entered_staff_id        int             not null,
	date_entered            datetime        not null,
	last_change_staff_id    int             not null,
	last_change_date        datetime        not null
)
go

sp_primarykey garden_location, garden_loc_id
go

/*append dummy record for locations with no parent */
insert garden_location (
	garden_loc_id,
	loc_type,
	loc_name, 
	loc_abbrev,
	parent_id,
	full_loc_name)
values (0,
	'collection',
	'none',
	'',
	-1,
	'none')
go

sp_bindefault zero_default, 'garden_location.entered_staff_id'
go

sp_bindefault zero_default, 'garden_location.last_change_staff_id'
go

sp_bindefault date_default, 'garden_location.date_entered'
go

sp_bindefault date_default, 'garden_location.last_change_date'
go

sp_bindefault null_par_default, 'garden_location.loc_name'
go

sp_bindrule loc_type_rule, 'garden_location.loc_type'
go

sp_foreignkey accession_activity, garden_location, garden_loc_id
go

sp_foreignkey plant_location, garden_location, garden_loc_id
go

sp_foreignkey prop_destination, garden_location, garden_loc_id
go

sp_foreignkey garden_location, staff, entered_staff_id
go

sp_foreignkey garden_location, staff, last_change_staff_id
go

create unique clustered index PKgarden_location on garden_location (garden_loc_id)
go

create index AK1garden_location on garden_location (parent_id)
go

create index IF1garden_location on garden_location (entered_staff_id)
go

create index IF2garden_location on garden_location (last_change_staff_id)
go


