Connecting the database to the outside world with Perl and Database Events

create_tables.sql

/*
##Author:Nikos Vaggalis
##Licensed under Artistic License 1.0
##Accompanying code of the "Connecting the database to the outside world with Perl and Database Events" 
##article on i-programmer.info
https://www.i-programmer.info/programming/perl/12299-connecting-the-database-to-the-outside-world-with-perl-and-database-events.html
*/

create table patientassignor(
      universal_id  varchar(20) not null not default,
	  namespace_id  varchar(199) not null not default,
      universal_id_type  varchar(6) not null not default,
PRIMARY KEY ( universal_id )
with STRUCTURE=BTREE)
;

create table patientregistry(
    patient_id  integer not null not default,
	ssn integer not null not default,
	family_name varchar(50) not null not default, 
	given_name  varchar(50) not null not default,
	dob date not null default ' ',
	sex char(1)  not null not default,
	phone_number  varchar(50) not null not default,
	assignors_id  varchar(20) not null not default,
PRIMARY KEY (patient_id), 
UNIQUE (ssn),
FOREIGN KEY (assignors_id)
REFERENCES patientassignor (universal_id)
with STRUCTURE=BTREE)
;
create table patientevent(
    patient_id  integer not null not default,
	event_type char(3) not null not default, /*A01*/
	event_id integer not null not default,
	event_date_time  timestamp(6) without time zone default CURRENT_TIMESTAMP,
	sending_org char(5) not null not default,
	PRIMARY KEY (patient_id,event_type, event_id),
FOREIGN KEY ( patient_id) 
REFERENCES patientregistry (patient_id )
with STRUCTURE=BTREE)
;
create table patientvisit(
    patient_id  integer not null not default,
	event_type char(3) not null not default,
	event_id integer not null not default,
	patient_class char(1) not null not default,
	attending_doctor_id integer not null not default,
	referring_doctor_id integer not null not default,
	hospital_service  char(1) not null not default,
	admit_source  smallint  not null not default,
	total_charges money not null default 0, 
	total_adjustments money not null default 0, 
	total_payments money not null default 0, 
	PRIMARY KEY (patient_id,event_type, event_id),
	FOREIGN KEY (patient_id,event_type, event_id)
	REFERENCES patientevent (patient_id ,event_type, event_id)
	with STRUCTURE=BTREE)
;
create table patientdiagnosis(
    patient_id  integer not null not default,
        event_type char(3) not null not default,
        event_id integer not null not default,
        diagnosis_id  char(5) not null not default,
        diagnosing_clinician_id integer not null not default,
        PRIMARY KEY ( patient_id,event_type, event_id,diagnosis_id),
        FOREIGN KEY (patient_id,event_type, event_id)
        REFERENCES patientevent (patient_id ,event_type, event_id)
        with STRUCTURE=BTREE)
;