Sample ER Diagram for Patient/Doctor relationship

CREATE TABLE patient (
	patient_id INT PRIMARY KEY AUTO_INCREMENT,
	last_name VARCHAR(50) NOT NULL,
	first_name VARCHAR(50) NOT NULL);


CREATE TABLE providers (
	provider_id INT PRIMARY KEY AUTO_INCREMENT,
	last_name VARCHAR(50) NOT NULL,
	first_name VARCHAR(50) NOT NULL);

CREATE TABLE procedures (
	procedure_id INT PRIMARY KEY AUTO_INCREMENT,
	description VARCHAR(50) NOT NULL);


CREATE TABLE encounters (
	encounter_id INT PRIMARY KEY AUTO_INCREMENT,
	patient_id INT REFERENCES patient(patient_id),
	provider_id INT REFERENCES provider(provider_id),
	procedure_id INT REFERENCES procedures(procedure_id ),
	encounter_dt DATETIME);