In Patient Medications

Description of SQL DLL

USER Table

Attribute	Type	Description	Nullable
(PK) user_id	int4	A unique identifier for each user.	No
user_firstname	varchar	The first name of the user. Up to 255 characters.	No
user_lastname	varchar	The last name of the user. Up to 255 characters.	No
user_type	varchar	Professional credentials (e.g., MD, DO, RN). Can include roles like “IN” for intake/triage and other non-credentialed roles.	No
user_access	varchar	Access level within the system (Admin1, Admin2, Clinical).	No
npi	varchar	National Provider Identifier for healthcare providers in the U.S. Up to 255 characters.	Yes
user_phone	varchar	The phone number of the user. Up to 255 characters.	No
user_pager	varchar	The pager number of the user, relevant for healthcare professionals. Up to 255 characters.	Yes
user_dob	date	The date of birth of the user.	No
user_ssn	varchar	The Social Security Number of the user. Up to 255 characters.	No
user_email	varchar	The email address of the user. Up to 255 characters.	No
user_date_start	date	The start date of the user’s employment or association with the facility.	No
user_date_end	date	The end date of the user’s employment or association, if applicable.	Yes
user_status	varchar	The current status of the user (e.g., active, inactive). Up to 255 characters.	No
user_training_lvl	varchar	The training level or professional development stage (e.g., Resident, Attending).	No
(FK) user_dept	int4	The department ID to which the user is assigned. Links to department’s id in the “DEPARTMENT” table.	No
created_datetime	timestamp	The timestamp when the user’s account was created. Automatically set to the current timestamp.	No
(FK) created_by	int4	Identifier of the user who created this record. Links to another user’s id in the “USER” table.	No
updated_datetime	timestamp	Timestamp when the user record was last updated, if applicable.	Yes
(FK) updated_by	int4	Identifier of the user who last updated this record. Links to another user’s id in the “USER” table. May be null if not updated after creation.	Yes

DEPARTMENT Table

Attribute	Type	Description	Nullable
(PK) dept_id	int4	A unique identifier for each department.	No
(FK) dept_head_id	int4	The user ID of the department head. Links to the user’s ID in the “USER” table.	No
dept_phone	varchar	The contact phone number for the department. Up to 255 characters.	No
(FK) created_by	int4	Identifier of the user who created this department record. Links to the user’s id in the “USER” table.	No
created_datetime	timestamp	Timestamp when the department record was created. Automatically set to the current timestamp.	No
(FK) updated_by	int4	Identifier of the user who last updated this department record. Links to the user’s id in the “USER” table.	Yes
updated_datetime	timestamp	Timestamp when the department record was last updated, if applicable.	Yes
dept_name	varchar	The name of the department. Up to 255 characters.	Yes

DIAGNOSIS Table

Attribute	Type	Description	Nullable
(PK) dx_id	int4	A unique identifier for each diagnosis record.	No
(FK) pt_id	int4	Identifier for the patient this diagnosis belongs to. Links to the patient’s id in the “PATIENT” table.	No
dx_date_start	date	The start date of the diagnosis.	No
dx_date_end	date	The end date of the diagnosis, if applicable.	Yes
icd	varchar	The International Classification of Diseases (ICD) code for the diagnosis. Up to 255 characters.	No
dx_status	varchar	The status of the diagnosis (e.g., confirmed, pending). Up to 255 characters.	No
(FK) created_by	int4	Identifier of the user who created this diagnosis record. Links to the user’s id in the “USER” table.	No
created_datetime	timestamp	Timestamp when the diagnosis record was created. Automatically set to the current timestamp.	No
(FK) updated_by	int4	Identifier of the user who last updated this diagnosis record. Links to the user’s id in the “USER” table.	Yes
updated_datetime	timestamp	Timestamp when the diagnosis record was last updated, if applicable.	Yes

PATIENT Table

Attribute	Type	Description	Nullable
(PK) pt_id	int4	A unique identifier for each patient.	No
pt_firstname	varchar	The first name of the patient. Up to 255 characters.	No
pt_lastname	varchar	The last name of the patient. Up to 255 characters.	No
mrn	varchar	The medical record number for the patient. Unique within the healthcare facility.	No
pt_ssn	varchar	The Social Security Number of the patient. Up to 255 characters.	Yes
pt_dob	date	The date of birth of the patient.	No
(FK) pcp_id	int4	Identifier for the patient’s primary care physician. Links to the “USER” table.	No
pt_phone	varchar	The phone number of the patient. Up to 255 characters.	No
pt_email	varchar	The email address of the patient. Up to 255 characters.	Yes
pt_status	varchar	The current status of the patient (e.g., admitted, discharged). Up to 255 characters.	Yes
pt_location	varchar	The location of the patient within the healthcare facility. Up to 255 characters.	Yes
(FK) inpatient_physician_id	int4	Identifier of the inpatient attending physician. Links to the “USER” table.	Yes
created_datetime	timestamp	Timestamp when the patient record was created. Automatically set to the current timestamp.	No
(FK) created_by	int4	Identifier of the user who created this patient record. Links to the “USER” table.	No
updated_datetime	timestamp	Timestamp when the patient record was last updated, if applicable.	Yes
(FK) updated_by	int4	Identifier of the user who last updated this patient record. Links to the “USER” table.	Yes

ADDRESS Table

Attribute	Type	Description	Nullable
(PK) address_id	int4	A unique identifier for each address.	No
house_num	varchar	The house or building number of the address. Up to 255 characters.	No
city	varchar	The city of the address. Up to 255 characters.	No
zip	varchar	The postal code of the address. Up to 255 characters.	No
(FK) pt_id	int4	Identifier for the patient associated with this address. Links to the “PATIENT” table.	No
(FK) updated_by	int4	Identifier of the user who last updated this address record. Links to the “USER” table.	Yes
updated_datetime	timestamp	Timestamp when the address record was last updated, if applicable.	Yes
street_name	varchar	The street name of the address. Up to 255 characters.	Yes

MEDICATION_ORDER Table

Attribute	Type	Description	Nullable
(PK) order_id	int4	A unique identifier for each medication order.	No
(FK) pt_id	int4	Identifier for the patient to whom the medication is prescribed. Links to the patient’s id in the “PATIENT” table.	No
(FK) ordering_physician_id	int4	Identifier of the physician who made the medication order. Links to the user’s id in the “USER” table.	No
order_datetime	timestamp	The date and time when the medication order was placed. Automatically set to the current timestamp.	No
(FK) updated_by	int4	Identifier of the user who last updated this medication order. Links to the user’s id in the “USER” table.	Yes
updated_datetime	timestamp	Timestamp when the medication order was last updated, if applicable.	Yes

INPATIENT_MEDICATION Table

Attribute	Type	Description	Nullable
(PK) med_id	int4	A unique identifier for each inpatient medication record.	No
(FK) order_id	int4	Identifier for the medication order this inpatient medication is associated with. Links to the order’s id in the “MEDICATION_ORDER” table.	No
(FK) pt_id	int4	Identifier for the patient receiving the medication. Links to the patient’s id in the “PATIENT” table.	No
med_name	varchar	The name of the medication. Up to 255 characters.	No
med_dose	varchar	The dosage of the medication. Up to 255 characters.	No
med_freq	varchar	The frequency at which the medication is to be administered. Up to 255 characters.	No
med_route	varchar	The route of administration for the medication. Up to 255 characters.	No
med_instruction	text	Additional instructions for administering the medication.	Yes
med_date_start	date	The start date for the medication administration.	No
med_date_end	date	The end date for the medication administration, if applicable.	Yes
med_fill_datetime	timestamp	The date and time when the medication was last filled.	Yes
(FK) pharmacist_id	int4	Identifier of the pharmacist who dispensed the medication. Links to the user’s id in the “USER” table.	Yes
med_status	varchar	The status of the medication order (e.g., active, discontinued). Up to 255 characters.	No
(FK) dx_id	int4	Identifier for the diagnosis associated with the medication. Links to the diagnosis id in the “DIAGNOSIS” table.	No
med_refill_quant	numeric	The quantity of medication prescribed for refills.	Yes
med_refill_exp_date	date	The expiration date for medication refills, if applicable.	Yes
(FK) updated_by	int4	Identifier of the user who last updated this inpatient medication record. Links to the user’s id in the “USER” table.	Yes
updated_datetime	timestamp	Timestamp when the inpatient medication record was last updated, if applicable.	Yes

SQL DLL

CREATE SCHEMA public AUTHORIZATION rawlingslyle;
COMMENT ON SCHEMA public IS 'standard public schema';


-- public."ADDRESS" definition
CREATE TABLE public."ADDRESS" (
    address_id int4 NOT NULL,
    house_num varchar(255) NOT NULL,
    city varchar(255) NOT NULL,
    zip varchar(255) NOT NULL,
    pt_id int4 NOT NULL,
    updated_by int4 NULL, -- User ID of the person who last updated the record. For logging purposes, not a requirement for record validity.
    updated_datetime timestamp NULL DEFAULT CURRENT_TIMESTAMP,
    street_name varchar(255) NULL,
    CONSTRAINT "ADDRESS_pkey" PRIMARY KEY (address_id)
);

-- Column comments
COMMENT ON COLUMN public."ADDRESS".updated_by IS 'User ID of the person who last updated the record. For logging purposes, not a requirement for record validity.';


-- public."DEPARTMENT" definition
CREATE TABLE public."DEPARTMENT" (
    dept_id int4 NOT NULL,
    dept_head_id int4 NOT NULL,
    dept_phone varchar(255) NOT NULL,
    created_by int4 NOT NULL,
    created_datetime timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_by int4 NULL, -- User ID of the person who last updated the record. For logging purposes, not a requirement for record validity.
    updated_datetime timestamp NULL DEFAULT CURRENT_TIMESTAMP,
    dept_name varchar(255) NULL,
    CONSTRAINT "DEPARTMENT_ak_1" UNIQUE (dept_head_id),
    CONSTRAINT "DEPARTMENT_pkey" PRIMARY KEY (dept_id)
);

-- Column comments
COMMENT ON COLUMN public."DEPARTMENT".updated_by IS 'User ID of the person who last updated the record. For logging purposes, not a requirement for record validity.';


-- public."DIAGNOSIS" definition
CREATE TABLE public."DIAGNOSIS" (
    dx_id int4 NOT NULL,
    pt_id int4 NOT NULL,
    dx_date_start date NOT NULL,
    dx_date_end date NULL,
    icd varchar(255) NOT NULL,
    dx_status varchar(255) NOT NULL,
    created_by int4 NOT NULL,
    created_datetime timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_by int4 NULL, -- User ID of the person who last updated the record. For logging purposes, not a requirement for record validity.
    updated_datetime timestamp NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT "DIAGNOSIS_pkey" PRIMARY KEY (dx_id)
);

-- Column comments
COMMENT ON COLUMN public."DIAGNOSIS".updated_by IS 'User ID of the person who last updated the record. For logging purposes, not a requirement for record validity.';


-- public."INPATIENT_MEDICATION" definition
CREATE TABLE public."INPATIENT_MEDICATION" (
    med_id int4 NOT NULL,
    order_id int4 NOT NULL,
    pt_id int4 NOT NULL,
    med_name varchar(255) NOT NULL,
    med_dose varchar(255) NOT NULL,
    med_freq varchar(255) NOT NULL,
    med_route varchar(255) NOT NULL,
    med_instruction text NOT NULL,
    med_date_start date NOT NULL,
    med_date_end date NULL,
    med_fill_datetime timestamp NULL,
    pharmacist_id int4 NULL,
    med_status varchar(255) NOT NULL,
    dx_id int4 NOT NULL,
    med_refill_quant numeric NULL,
    med_refill_exp_date date NULL,
    CONSTRAINT "INPATIENT_MEDICATION_ak_1" UNIQUE (pharmacist_id),
    CONSTRAINT "INPATIENT_MEDICATION_pkey" PRIMARY KEY (med_id)
);


-- public."MEDICATION_ORDER" definition
CREATE TABLE public."MEDICATION_ORDER" (
    order_id int4 NOT NULL,
    order_datetime timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    pt_id int4 NOT NULL,
    ordering_physician_id int4 NOT NULL,
    updated_datetime timestamp NULL DEFAULT CURRENT_TIMESTAMP,
    updated_by int4 NULL, -- User ID of the person who last updated the record. For logging purposes, not a requirement for record validity.
    CONSTRAINT "MEDICATION_ORDER_pkey" PRIMARY KEY (order_id)
);

-- Column comments
COMMENT ON COLUMN public."MEDICATION_ORDER".updated_by IS 'User ID of the person who last updated the record. For logging purposes, not a requirement for record validity.';

-- public."PATIENT" definition
CREATE TABLE public."PATIENT" (
    pt_id int4 NOT NULL,
    pt_firstname varchar(255) NOT NULL,
    pt_lastname varchar(255) NOT NULL,
    mrn varchar(255) NOT NULL,
    pt_ssn varchar(255) NULL,
    pt_dob date NOT NULL,
    pcp_id int4 NOT NULL,
    pt_phone varchar(255) NOT NULL,
    pt_email varchar(255) NULL,
    pt_status varchar(255) NULL,
    pt_location varchar(255) NULL,
    inpatient_physician_id int4 NULL,
    created_datetime timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created_by int4 NOT NULL,
    updated_datetime timestamp NULL DEFAULT CURRENT_TIMESTAMP,
    updated_by int4 NULL, -- User ID of the person who last updated the record. For logging purposes, not a requirement for record validity.
    CONSTRAINT "PATIENT_ak_2" UNIQUE (updated_by),
    CONSTRAINT "PATIENT_pkey" PRIMARY KEY (pt_id)
);

-- Column comments
COMMENT ON COLUMN public."PATIENT".updated_by IS 'User ID of the person who last updated the record. For logging purposes, not a requirement for record validity.';


-- public."USER" definition
CREATE TABLE public."USER" (
    user_id int4 NOT NULL,
    user_firstname varchar(255) NOT NULL,
    user_lastname varchar(255) NOT NULL,
    user_type varchar NOT NULL, -- Professional credentials such as MD, DO, RN, DPM, RT, etc. If no credentials, then custom job credientials such as (IN) for intake.
    user_access varchar(255) NOT NULL,
    npi varchar(255) NOT NULL,
    user_phone varchar(255) NOT NULL,
    user_pager varchar(255) NOT NULL,
    user_dob date NOT NULL,
    user_ssn varchar(255) NOT NULL,
    user_email varchar(255) NOT NULL,
    user_date_start date NOT NULL,
    user_date_end date NULL,
    user_status varchar(255) NOT NULL,
    user_training_lvl varchar(255) NOT NULL,
    user_dept int4 NOT NULL,
    created_datetime timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created_by int4 NOT NULL,
    updated_datetime timestamp NULL DEFAULT CURRENT_TIMESTAMP,
    updated_by int4 NULL, -- User ID of the person who last updated the record. For logging purposes, not a requirement for record validity.
    CONSTRAINT "PHYSICIAN_pkey" PRIMARY KEY (user_id)
);

-- Column comments
COMMENT ON COLUMN public."USER".user_type IS 'Professional credentials such as MD, DO, RN, DPM, RT, etc. If no credentials, then custom job credientials such as (IN) for intake.';
COMMENT ON COLUMN public."USER".updated_by IS 'User ID of the person who last updated the record. For logging purposes, not a requirement for record validity.';


-- public."ADDRESS" foreign keys
ALTER TABLE public."ADDRESS" ADD CONSTRAINT "ADDRESS_fk4" FOREIGN KEY (pt_id) REFERENCES public."PATIENT"(pt_id);
ALTER TABLE public."ADDRESS" ADD CONSTRAINT user_id_updated_by FOREIGN KEY (updated_by) REFERENCES public."USER"(user_id);


-- public."DEPARTMENT" foreign keys
ALTER TABLE public."DEPARTMENT" ADD CONSTRAINT user_id_created_by FOREIGN KEY (created_by) REFERENCES public."USER"(user_id);
ALTER TABLE public."DEPARTMENT" ADD CONSTRAINT user_id_dept_head_id FOREIGN KEY (dept_head_id) REFERENCES public."USER"(user_id);
ALTER TABLE public."DEPARTMENT" ADD CONSTRAINT user_id_updated_by FOREIGN KEY (updated_by) REFERENCES public."USER"(user_id);


-- public."DIAGNOSIS" foreign keys
ALTER TABLE public."DIAGNOSIS" ADD CONSTRAINT "DIAGNOSIS_fk1" FOREIGN KEY (pt_id) REFERENCES public."PATIENT"(pt_id);
ALTER TABLE public."DIAGNOSIS" ADD CONSTRAINT user_id_created_by FOREIGN KEY (created_by) REFERENCES public."USER"(user_id);
ALTER TABLE public."DIAGNOSIS" ADD CONSTRAINT user_id_updated_by FOREIGN KEY (updated_by) REFERENCES public."USER"(user_id);


-- public."INPATIENT_MEDICATION" foreign keys
ALTER TABLE public."INPATIENT_MEDICATION" ADD CONSTRAINT "INPATIENT_MEDICATION_fk1" FOREIGN KEY (order_id) REFERENCES public."MEDICATION_ORDER"(order_id);
ALTER TABLE public."INPATIENT_MEDICATION" ADD CONSTRAINT "INPATIENT_MEDICATION_fk10" FOREIGN KEY (dx_id) REFERENCES public."DIAGNOSIS"(dx_id);
ALTER TABLE public."INPATIENT_MEDICATION" ADD CONSTRAINT "INPATIENT_MEDICATION_fk2" FOREIGN KEY (pt_id) REFERENCES public."PATIENT"(pt_id);
ALTER TABLE public."INPATIENT_MEDICATION" ADD CONSTRAINT user_id_pharm_id FOREIGN KEY (pharmacist_id) REFERENCES public."USER"(user_id);


-- public."MEDICATION_ORDER" foreign keys
ALTER TABLE public."MEDICATION_ORDER" ADD CONSTRAINT "MEDICATION_ORDER_fk3" FOREIGN KEY (pt_id) REFERENCES public."PATIENT"(pt_id);
ALTER TABLE public."MEDICATION_ORDER" ADD CONSTRAINT "MEDICATION_ORDER_fk4" FOREIGN KEY (ordering_physician_id) REFERENCES public."USER"(user_id);
ALTER TABLE public."MEDICATION_ORDER" ADD CONSTRAINT "MEDICATION_ORDER_fk6" FOREIGN KEY (updated_by) REFERENCES public."USER"(user_id);


-- public."PATIENT" foreign keys
ALTER TABLE public."PATIENT" ADD CONSTRAINT "PATIENT_fk6" FOREIGN KEY (pcp_id) REFERENCES public."USER"(user_id);
ALTER TABLE public."PATIENT" ADD CONSTRAINT "PATIENT_fk9" FOREIGN KEY (inpatient_physician_id) REFERENCES public."USER"(user_id);
ALTER TABLE public."PATIENT" ADD CONSTRAINT user_id_created_by FOREIGN KEY (created_by) REFERENCES public."USER"(user_id);
ALTER TABLE public."PATIENT" ADD CONSTRAINT user_id_updated_by FOREIGN KEY (updated_by) REFERENCES public."USER"(user_id);


-- public."USER" foreign keys
ALTER TABLE public."USER" ADD CONSTRAINT "PHYSICIAN_fk13" FOREIGN KEY (user_dept) REFERENCES public."DEPARTMENT"(dept_id);
ALTER TABLE public."USER" ADD CONSTRAINT user_id_created_by FOREIGN KEY (created_by) REFERENCES public."USER"(user_id);
ALTER TABLE public."USER" ADD CONSTRAINT user_id_updated_by FOREIGN KEY (updated_by) REFERENCES public."USER"(user_id);

SQL DML

-- I have to disable dependecies for the first user/department"
ALTER TABLE "DEPARTMENT" DISABLE TRIGGER ALL;
ALTER TABLE "USER" DISABLE TRIGGER ALL;

-- DEPARTMENT table
INSERT INTO "DEPARTMENT" (dept_id, dept_head_id, dept_phone, created_by, dept_name)
VALUES (1, 1, '555-1234', 1, 'Dermatology');

-- USER table
INSERT INTO "USER" (user_id, user_firstname, user_lastname, user_type, user_access, npi, user_phone, user_pager, user_dob, user_ssn, user_email, user_date_start, user_status, user_training_lvl, user_dept, created_by)
VALUES (1, 'Rony', 'Patel', 'physician', 'admin1', '123456789', '555-1234', '555-5678', '1980-01-01', '123-45-6789', 'ronypatel@example.com', '1950-01-01', 'active', 'attending', 1, 1);

INSERT INTO "USER" (user_id, user_firstname, user_lastname, user_type, user_access, npi, user_phone, user_pager, user_dob, user_ssn, user_email, user_date_start, user_status, user_training_lvl, user_dept, created_by)
VALUES (2, 'Kim', 'Liu', 'pharmacist', 'admin2', '123456789', '555-1234', '555-5678', '1980-01-01', '123-45-6789', 'kimliu@example.com', '1950-01-01', 'active', 'attending', 1, 1);
ALTER TABLE "DEPARTMENT" ENABLE TRIGGER ALL;
ALTER TABLE "USER" ENABLE TRIGGER ALL;

-- PATIENT table
INSERT INTO "PATIENT" (pt_id, pt_firstname, pt_lastname, mrn, pt_ssn, pt_dob, pcp_id, pt_phone, pt_email, pt_status, pt_location, inpatient_physician_id, created_by)
VALUES (1, 'Karen', 'Juncal', 'MRN001', '987-65-4321', '1990-02-02', 1, '555-2345', 'karenjuncal@example.com', 'admitted', 'Room 101', 1, 1);

-- Insert data into ADDRESS table
INSERT INTO "ADDRESS" (address_id, house_num, street_name, city, zip, pt_id)
VALUES (1, '123','Main St', 'Anytown', '12345', 1);

-- DIAGNOSIS table
INSERT INTO "DIAGNOSIS" (dx_id, pt_id, dx_date_start, icd, dx_status, created_by)
VALUES (1, 1, '2023-01-10', 'A01.0', 'confirmed', 1);

-- MEDICATION_ORDER table
INSERT INTO "MEDICATION_ORDER" (order_id, pt_id, ordering_physician_id)
VALUES (1, 1, 1);

-- INPATIENT_MEDICATION table
INSERT INTO "INPATIENT_MEDICATION" (med_id, order_id, pt_id, med_name, med_dose, med_freq, med_route, med_instruction, med_date_start, pharmacist_id, med_status, dx_id, med_refill_quant)
VALUES (1, 1, 1, 'Sertraline', '50mg', 'Once Daily', 'Oral', 'Take with food', '2023-01-11', 2, 'Active',1, 1);