/* person */
CREATE TABLE person (
	person_id               key_t NOT NULL,
	prefix                  varchar(20) NULL,
	last_name               varchar(60) NOT NULL,
	first_name              varchar(80) NULL,
	middle_name             varchar(40) NULL,
	suffix                  varchar(20) NULL,
	birth_date              datetime NULL,
	death_date              datetime NULL,
	person_remark           varchar(255) NULL,
	data_source_id          int NOT 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

CREATE UNIQUE CLUSTERED INDEX XPKperson ON person (
	person_id,
	last_name,
	first_name,
	middle_name,
	birth_date
)
go

CREATE UNIQUE INDEX AK1person ON person (
        last_name,
	first_name,
	middle_name,
	birth_date,
	person_id
)
go

CREATE INDEX XIF1person ON person (entered_staff_id)
go

CREATE INDEX XIF2person ON person (last_change_staff_id)
go

exec sp_primarykey person, person_id
go

sp_bindefault zero_default, 'person.data_source_id'
go

sp_bindefault zero_default, 'person.entered_staff_id'
go

sp_bindefault zero_default, 'person.last_change_staff_id'
go

sp_bindefault date_default, 'person.date_entered'
go

sp_bindefault date_default, 'person.last_change_date'
go

exec sp_foreignkey person, agent, person_id
go

exec sp_foreignkey person, staff, entered_staff_id
go

exec sp_foreignkey person, staff, last_change_staff_id
go

