Skip to content

Instantly share code, notes, and snippets.

@nikosvaggalis
Last active November 19, 2018 19:57
Show Gist options
  • Save nikosvaggalis/767ab4933c7f3c868cab6a570a6fbe7d to your computer and use it in GitHub Desktop.
Save nikosvaggalis/767ab4933c7f3c868cab6a570a6fbe7d to your computer and use it in GitHub Desktop.
Connecting the database to the outside world with Perl and Database Events
/*
##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)
;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment