---------------------------
Proposed LinuDent table layout...
---------------------------
Based on FreeMed table structure. Thanks http://www.freemed.org
Version: 19990619
This is a "ROUGHT DRAFT" version
PLEASE send any comments/corrections on this file to SuperLinux@usa.net) so
that I may include them in future versions...
-------------------------------------------------------------------------------
-------------------------------------------------------------------------------
There are several inconsistencies throughout the database. A phone number
should be the same data type whether it is in the patient, insurance or doctor
table. Other fields that come to mind include assignment, email, websites.
The database should be updated so that these types are consistent from table
to table.
-------------------------------------------------------------------------------
This file attempts to document the structure of each table, and the relation
between them. Any errors should be brought to my attention so that corrections
can be made...
Each table is contains an "id" field which is the unique identifier for each
record. For example, each procedure contains a column called addproact, which
contains an INT UNSIGNED which corresponds to a row in the patient table
with the same id field.
###########
table test
###########
Variable Name Type Description
------------------------------------------------------------------------------
name CHAR(10) Name???
other CHAR(12) Other???
phone INT Phone number, perhaps???
ID INT UNSIGNED unique id # for each record
Primary Key: ID
Table Relations: ?
###########
table physician
###########
Variable Name Type Description
------------------------------------------------------------------------------
phylname CHAR(12) Doctor/Provider's last name
phyfname CHAR(10) Doctor/Provider's first name
phytitle CHAR(10) title (M.D, D.O, etc)
phymi CHAR(1) Doctor/Provider's middle name ^1phypracname CHAR(30) Practice Name ^2phyaddr1a CHAR(30) Practice Address line 1
phyaddr2a CHAR(30) Practice Address line 2
phycitya CHAR(20) Practice City
phyzipa CHAR(10) Practice Zip
phyphonea CHAR(10) Practice Phone #
phyfaxa CHAR(10) Practice Fax #
phyaddr1b CHAR(30) ??? Address line 1
phyaddr2b CHAR(30) ??? Address line 2
phycityb CHAR(20) ??? City
phyzipb CHAR(10) ??? Zip
phyphoneb INT UNSIGNED ??? Phone
phyfaxb INT UNSIGNED ??? Fax #
phyemail CHAR(30) Doctor's email address
phyupin INT UNSIGNED Doctor's UPIN # ^3physsn CHAR(9) Doctor's SSN/Tax ID#
physpe1 INT UNSIGNED Doctor's Primary Specialty
physpe2 INT UNSIGNED Doctor's Second Specialty??
physpe3 INT UNSIGNED Doctor's Third Specialty??
phyid1 CHAR(10) Doctor's Other ID#
phystatus INT UNSIGNED Doctor's status (perhaps used
so that doctors who have left
a practice can be "deactivated"
so charges and payments aren't
posted to them in error??)
phyref ENUM "yes" "no" Referring doctor?
(Perhaps used to abstract both
practice doctors and
non-practice doctors info into
a single table??)
phyrefcount INT UNSIGNED Number of referrals for a
referring doctor??
phyrefamt REAL Total Charges for this ref dr?
Total Charges for this prac dr?phyrefcoll REAL Total Collections for dr??
id INT NOT NULL unique record id
Primary Key: id
Notes:
Consistency?? Why is phyphonea a CHAR(10), and phyphoneb a INT UNSIGNED??
Consolidate Practice physician and Referring physician data?
###########
table icd9
###########
Variable Name Type Description
------------------------------------------------------------------------------
icd9code CHAR(6) icd9 code
icd10code CHAR(7) icd10 code
icd9descrip CHAR(45) Diagnosis V9 description
icd10descrip CHAR(45) Diagnosis V10 description
icdng DATE date for what?? ^1icddrg CHAR(45) ???
icdnum INT UNSIGNED ???
icdamt REAL Charge for Diag??
icdcoll REAL Collections for Diag??
id INT NUT NULL unique id#
Primary Key: id
Notes:
ICD9/ICD10 how searchable?
###########
table patient
###########
Variable Name Type Description
------------------------------------------------------------------------------
ptacct CHAR(10) patient Account #
ptupdt DATE date last changed??
ptbillunique INT UNSIGNED This seems extraneous, and a
dead give-away that medical
medical manager is being copied. Used to give "sequence" to
posting order of charges and
payments in MM.
ptbal REAL Account Balance
ptbalfwd REAL Bal Fwd (Used in Med Mgr for
"balance forward" statements.
ptunapp REAL Unapplied Credit (Until V9, the
"black hole" of the Medical
Manager's accounting system).
ptdoa DATE Date account created
ptrefdoc INT UNSIGNED Patient Referring Doctor
ptpcp INT UNSIGNED Patient "default doctor" for
procedure entry.
ptphy1 INT UNSIGNED ???
ptphy2 INT UNSIGNED ???
ptphy3 INT UNSIGNED ???
ptphy4 INT UNSIGNED ???
ptbilltype ENUM sta, mon, chg Bill type ^1ptbudg REAL Monthly payment amount???
ptnoplan INT UNSIGNED number of insurance plans??
ptnodep INT UNSIGNED number of dependents
(family billing)
ptdoc INT UNSIGNED ???Redundant with pcp???
ptlname CHAR(12) Patient Last Name
ptfname CHAR(10) Patient First Name
ptmi CHAR(1) Patient middle
ptaddr1 VARCHAR(30) Patient Address Line 1
ptaddr2 VARCHAR(30) Patient Address Line 2
ptcity CHAR(25) Patient City
ptstate CHAR(2) Patient State
ptzip CHAR(10) Patient zip code
pthphone CHAR(10) Patient home phone
ptwphone CHAR(10) Patient work phone
ptwphoneext VARCHAR(5) Patient work ext.
ptfax CHAR(10) Patient fax #
ptemail CHAR(30) Patient email
ptsex ENUM m f Patient sex
ptdob DATE Patient date of birth
ptssn CHAR(9) Patient soc sec number
ptdmv CHAR(9) Patient drivers license #
ptdtlpay DATE Date of last pat payment
patamtlpay REAL Amount of last pat payment
ptpaytime INT UNSIGNED ???
ptdtbill DATE Date last statement mailed
ptamtbill REAL Amount of last statement
ptstatus INT UNSIGNED ???
ptytdchg REAL Year to date charges
ptar REAL Amount due from pat
ptextinf TEXT ????
ptdisc REAL Discount (%)??
ptdol DATE Date of last visit??
ptdiag1 INT UNSIGNED Last Diag 1
ptdiag2 INT UNSIGNED Last Diag 2
ptdiag3 INT UNSIGNED Last Diag 3
ptdiag4 INT UNSIGNED Last Diag 4
ptid VARCHAR(10) Other patient ID#
pthistbal REAL (Medical Manager Hold-over)
Balance of purged history items
ptmarital ENUM s m d Patient Marital status
ptempl ENUM y n ???
ptemp1 INT UNSIGNED ???
ptemp2 INT UNSIGNED ???
ptdep INT UNSIGNED ???
id INT NOT NULL unique ID#
Comments:
---------
1) Patient Name (see doctor table above) should be able to accomidate
E. John Kleinschlogger III.
###########
table procedure
###########
Variable Name Type Description
------------------------------------------------------------------------------
addproacct INT UNSIGNED Referrs to the patient.id
for this procedure??
addproacctdep INT UNSIGNED Redundant info if *BOTH*
guarantors and deps are stored
in the patient table.
addproincident INT UNSIGNED I guess this would refer to
a record in what Medical Mgr
calls the ailment file???
addprodtmod DATE Date this record last modified?
addprochg REAL Charge for this item
addprorcp REAL Actual Money received for item
addproadj REAL Amount of Write/Offs for item
addprocode INT UNSIGNED I'm guessing the CPT-IV or
HCSPCS id for the item
addpromod1 INT UNSIGNED id of Modifier #1 for item
addpromod2 INT UNSIGNED id of Modifier #2 for item
addpromod3 INT UNSIGNED id of Modifier #3 for item
addprodx1 INT UNSIGNED id of icd9/icd10 diagnosis #1
addprodx2 INT UNSIGNED id of icd9/icd10 diagnosis #2
addprodx3 INT UNSIGNED id of icd9/icd10 diagnosis #3
addprodx4 INT UNSIGNED id of icd9/icd10 diagnosis #4
addprocom VARCHAR(45) procedure comment
addproprov INT UNSIGNED doctor.id of performing dr
addpropos INT UNSIGNED Place of Service Code
addprotos INT UNSIGNED Type of Service Code
addprostatus INT UNSIGNED (See Note #1 Below)
addprodept INT UNSIGNED Department of charge
addprovouch INT UNSIGNED "Voucher" number (used for
encounter form tracking).
addpropriins INT UNSIGNED (See RANT #2 below)
addprosecins INT UNSIGNED (See RANT #2 below)
addprounits REAL Number of units of the proc.
addprotype ENUM "normal" Type of Charge Item
"finance"
addprodate DATE Date the service was rendered.
addprodtpost DATE Date the charge was posted.
addprodtpostrep DATE ?????
addprodtinsbill DATE (See RANT #2 below)
addprodtinsbill2 DATE (See RANT #2 below)
addproassign ENUM "yes" "no" Accept Assignment?
"estimate"
addprotax CHAR(5) Tax amount???
addproaprov REAL Approved amount (RANT #2)
addproemc ENUM "valid" Seems to mirror Medical Manager "passed_prebill" for electronic claims
"no_emc"
"emc_billed"
addproloc INT UNSIGNED Procedure Location
addproclmnum INT UNSIGNED ?????
addpromin REAL Anesthesia minutes???
addproplan INT UNSIGNED (RANT #2)
id INT NOT NULL unique id# for record
Primary Key : id
---------------
need to improve activity storing and reporting functions
###########
table facility
###########
Variable Name Type Description
------------------------------------------------------------------------------
psrname CHAR(25) Name of Facility
psraddr1 CHAR(25) address line 1
psraddr2 CHAR(25) address line 2
psrcity CHAR(15) city
psrstate CHAR(3) state
psrzip CHAR(10) zip
psrnote VARCHAR(40) Note???
psrdateentry DATE date of what???
psrdefphy INT UNSIGNED provider.id ??? (Why)
psrphone INT UNSIGNED phone #
psrfax CHAR(15) fax #
psremail CHAR(25) email address
id INT NOT NULL AUTO_INC unique id for record
Primary Key: id
:
###########
table room
###########
Variable Name Type Description
------------------------------------------------------------------------------
###########
table specialty
###########
Variable Name Type Description
------------------------------------------------------------------------------
###########
table insco
###########
Variable Name Type Description
------------------------------------------------------------------------------
inscodtadd DATE Date Plan added??
inscodtmod DATE Date Plan last changed??
insconame VARCHAR(50) Plan Name
inscoalias VARCHAR(30) Alias for Plan
inscoaddr1 VARCHAR(45) Address 1
inscoaddr2 VARCHAR(45) Address 2
inscocity VARCHAR(30) city
inscostate VARCHAR(3) state
inscozip CHAR(10) zip code
inscophone CHAR(10) phone #
inscofax CHAR(10) Fax #
inscocontact VARCHAR(100) Contact Person
inscoid CHAR(20) ????? NEIC # perhaps???
inscowebsite VARCHAR(100) Ins Co's web site
inscoemail VARCHAR(50) Ins Co's email
inscogroup INT UNSIGNED Insurance Group ID
inscotype INT UNSIGNED (Medicare/Medicaid/BCBS/etc)/etcinscoassign INT UNSIGNED Accept Assignment
(Default for procedure entry)
id INT NOT NULL AUTO_INC unique id# of record
Primary Key: id
Comments:
We need to be consistent with address lines, city, state
zip, phone numbers, email fields. Also, assign should be an enum like it
it in the procedure table.
###########
table inscogroup
###########
Variable Name Type Description
------------------------------------------------------------------------------
inscogroup VARCHAR(50) Name of Group
id INT NOT NULL AUTO_IN Unique # of record
Primary Key: id
Comments:
This seems to mirror the "carrier code" concept from MM.
END OF FILE.
Parts of this file are from a file copyright (C) 1999, Ryan Losh (rklosh@rkl.org), All rights
reserved. GNU GPL version 2.