ehr_create.sql

create database ehr;

use ehr;

CREATE TABLE patient 
(
	id INT NOT NULL AUTO_INCREMENT,
	name NOT NULL VARCHAR(255),
	d_id INT NOT NULL,
	password VARCHAR(255) NOT NULL,
	PRIMARY KEY (id),
	FOREIGN KEY (d_id) REFERENCES doctor(id)
);

CREATE TABLE doctor
(
	id INT NOT NULL AUTO_INCREMENT,
	name NOT NULL VARCHAR(255),
	password VARCHAR(255) NOT NULL,
	PRIMARY KEY (id)
);

CREATE TABLE pt_notes
(
	id INT NOT NULL AUTO_INCREMENT,
	p_id INT NOT NULL,
	date DATETIME DEFAULT CURRENT_TIMESTAMP,
	note TEXT NOT NULL,
	PRIMARY KEY (id),
	FOREIGN KEY (p_id) REFERENCES patient(id)
);

CREATE TABLE dr_notes
(
	id INT NOT NULL AUTO_INCREMENT,
	d_id INT NOT NULL,
	p_id INT NOT NULL,
	date DATETIME DEFAULT CURRENT_TIMESTAMP,
	note TEXT NOT NULL,
	PRIMARY KEY (id),
	FOREIGN KEY (d_id) REFERENCES doctor(id),
	FOREIGN KEY (p_id) REFERENCES patient(id)
);

create doctor before creating patient.


# Select patient

SELECT * FROM patient WHERE `id` = 1;

# Select notes for a patient

SELECT * FROM pt_notes WHERE `p_id` = 1;

# Select notes for a patient from a doctor

SELECT * FROM dr_notes WHERE `p_id` = 1;

# Get all notes by a doctor (id 1)

SELECT * FROM dr_notes WHERE `d_id` = 1 ORDER BY date DESC;

# Get all of a doctor's patients

SELECT * FROM patients WHERE `d_id` = 1;