Dear SQL/ER gurus
Here is my humble effort for a DB schema based on the recent discussions
No doubt there are some ways of improving it - I would be interested in
comments
I will put it up on the Wiki also
R
/* proposed data file to mysql for billing data tables */
-- At the shell command line enter
-- To load this file into a MySQL table
-- Create a database and grant permissions using the relevant user and
privileges eg:
-- su mysql
-- (password)
-- mysql Invoke the mysql editor
-- mysql> create database billing_development;
-- mysql> grant all on billing_development.* to '[user]'@'[host]';
-- enter your details to [ ]
-- mysql> exit
-- mysql -u [user] [database_name] < [datafile.sql]
-- (insert your own values in [ ] )
-- Fundamental Entity is a transaction
-- Other entities
-- Provider
-- Patient
-- (these should be available from the appointments module)
-- Payor
-- Item
-- transaction has relations to other entities:
-- Transaction has one and only one provider, payor, patient
-- Transaction has 1-5 items
-- Q: could item and payor be related?
-- eg transaction -> item -> payor
-- or transaction -> payor -> item
-- In Transaction table
-- Location of service code can be
-- practice in hours
-- practice after hours
-- home visit
-- nursing home
-- hospital
-- report
-- ?other
-- Status code ?
-- billed
-- paid
-- reconciled
-- doctor_paid
-- table billing_transaction
USE billing_development;
/* first clear old tables */
DROP TABLE IF EXISTS transaction;
DROP TABLE IF EXISTS payor;
DROP TABLE IF EXISTS item;
create table transaction(
id INT(8) NOT NULL AUTO_INCREMENT,
transaction_date DATE NOT NULL,
provider_id INT(3) NOT NULL,
payor_id INT(2) NOT NULL,
patient_id INT(6) NOT NULL,
location_of_service_code INT(1) NOT NULL,
notes VARCHAR(255),
item_1 VARCHAR(20) NOT NULL,
item_2 VARCHAR(20),
item_3 VARCHAR(20),
item_4 VARCHAR(20),
item_5 VARCHAR(20),
status_code INT(1) NOT NULL,
PRIMARY KEY (id)
);
/* Provider table is already set up elsewhere */
-- but should include
-- INT id
-- VARCHAR name
-- VARCHAR provider_number
--
-- ?other
/* Payor table */
create table payor(
id INT(2) NOT NULL AUTO_INCREMENT,
name VARCHAR(80) NOT NULL,
address_1 VARCHAR(80),
address_2 VARCHAR(80),
notes VARCHAR(80),
PRIMARY KEY (id)
);
/* Patient table set up already */
-- but should include
-- title
-- name
-- address
-- DOB
-- VARCHAR medicare_number
-- BOOLEAN HCC_status NOT NULL default none
-- VARCHAR HCC_number
-- VARCHAR Private_Insurance_number
/* Item No table Can we use a boolean value for GST_payable? */
create table item(
id INT(4) NOT NULL AUTO_INCREMENT,
date_updated DATE,
payor_id INT(2) NOT NULL,
notes VARCHAR(255),
amount_1 FLOAT,
amount_2 FLOAT,
GST_payable INT(1),
PRIMARY KEY (id)
);
_______________________________________________
Gpcg_talk mailing list
[email protected]
http://ozdocit.org/cgi-bin/mailman/listinfo/gpcg_talk
