MySQL help needed - Charity!

[BliarOut]

I am involved in a project to build a database to analyse trauma injuries.

At the moment, it's an old FM Pro database and not very well designed. It's being moved over to MySQL and I am assisting in the development.

Relationships are non existent and I want to tidy things up. I could do with some help in MySQL query syntax to assist me in my tasks.

As I am a newbie with this, I don't expect to be asking difficult questions, but if someone doesn't mind answering the odd question/making plain english out of the documentation I would really appreciate it.

Contact me through my profile if you can help.

Thanks,

Rob

[BliarOut]

Thanks Ted,

I'll mail you once I can get the questions coherent!

[BliarOut]

Thanks all. I'll pop questions up here as and when!

Firstly, the tables. There are tables for procedures, complications, injuries etc. Currently, the doctor or surgeon could be recorded as john doe in one table, in another table it would be [e-mail] and in another table, JDPK (two doctors).

I assume the right way to do things would be to create another table called TR_Users or similar, with all the doctors info in it, such as FirstName,LastName,EMail,Tel,Abbr etc. and have a unique autonumber type field DoctorID or similar.

Then I add a column to each of the other tables which will contain DoctorID. Then I would run a query... and here's where I will need the help , which will put the correct DoctorID into my existing tables.

I am assuming the right way to go is to end up only storing the DoctorID field in my tables and grab their details in a query whenever I want to display/update data.

Am I barking up the right tree? I can see I would only be storing up problems if I don't sure up the foundations before looking at rebuilding the app.

[BliarOut]

I'll try to post the structure up later.

In the meantime, can anyone define normalise in words of one sylabul or less. That, as I see it is the panacea I should be aiming for.

[BliarOut]

OK, here's what I have to work with... Note how the Dr/surgeon is stored with different naming conventions. The field names are in UPPERCASE, along with a textual description.

It's my intention to add fields to achieve normalisation and then drop the duplicates later on, once work has finished. This is actually in use, so I can't delete fields at this stage.

Once the structure has been updated as necessary, I'll need some queries to update the new fields from the existing data.

Any suggestions where to begin gratefully appreciated

Is there a standard way to "describe" this sort of stuff rather than the method I have usaed here?)



=========================== TR Tables & Fields

cnote -- clinical report table
-----
CID - record ID number
WUTYPE - type of report (operative , H&P (history and physical exam ), etc)
RDATE - report date
SURG - surgeon name
SECY - secretary name
MSURG - surgeon email
MSECY - secretary email
ETXT - report text

comps - complications table
------
ID - record ID number
MRN - medical record number
CDate - complication date
ATT - attending physicians (usually just the initials e.g. HJ/MN etc)
COMP - complication descriptors
PROG - progress notes and complication treatment

ortx - images table
----
PICNO - record ID number
AGE - age
DOB - date of birth
DOC - doctor
FILENAME - image file name e.g. XR00089.jpg
FURL - file URL on server http://server.com/xrays/XR00089.jpg
MODE - Image type XR - xray, CP - clinical photograph etc.
MRN - medical record number
PTNAME - Lat name, First Name of patient
STUDYDATE - date of image
STUDYTYPE - description of study e.g. AP Xray Rt femur

preop - operative scheduling table
------
SID - record ID #
FIRST -
LAST -
STREET -
CITY -
STATE -
ZIP -
PHONE -
MRN - medical record number
ADMIT - admit date
RSDATE - record creation date
SOURCE - where the appointment is made
JOB - notes, comments
SURGEON - surgeon e-mail
SECY - secretary e-mail
EDIT_DATE - last edit date
EDIT_TIME - last edit time
ANES - anesthesia
EQPT - special instruments needed for surgery
INS - Insurance company
DOB - Date of birth
DX - diagnoses
RX - treatments
REQDATE - requested operation date
HSTAY - anticipated hospital stay
RXDUR - operation duration
FOTO - photo required
PRECERT - precertification
POLNO - Insurance policy number
XRAY - xrays required
DANES - date of anesthesia H&P
TANES - time of anesthesia H&P
DPREOP - date of preop H&P
TPREOP - time of preop H&P
DOR - date of operation
TOR - time of operation
CASENO - case number
SUR1 - surgeon name
SEC1 - secretary name
OPLOC - operation location
HXPX - H&P yes/no
OROOM - operating room
RDOC - referring physician

proc - procedures and billing table
--------
PID - record number
MRN - medical record number
CLAINO - claim number
FIRST - first name patient
DCLAIM - date of claim
DINJ - date of injury
ADM - date of admission to hospital
MVA - Motor vehicle accident Yes/No
WREL - Work related Yes/no
A1DOC - first assistant
A2DOC - second assistant
SDOC - surgeon
ADOC - attending physician
RDOC - referring physician
POS - place of surgery
ICDNO - International Code of Disease
ICDNAME - ICD natural language descriptor
CPTDATE - date of procedure(s)
CHARGE - the charges
RDOCID - referring doctor ID#
ADOCID - attending doctor ID #
DEPID - department ID #
DEPT - Department name
RVU1 - Relative Value Unit number 1
RVU2 - Relative Value Unit number 2
RVU3 - Relative Value Unit number 3
RVU4 - Relative Value Unit number 4
RVU5 - Relative Value Unit number 5
RVU6 - Relative Value Unit number 6
RVUTOT - total RVU's
FNAME - unused global variable
INS - insurance company
POLNO - policy number
EDIT_TIME - last edit time of this record
EDIT_DATE - last edit date of this record
CPT1 - AMA treatment code 1
CPTNAME1 - AMA treatment code 1 natural language descriptor
MOD15 - CPT modifier - first CPT code - fifth modifier
MOD14 - CPT modifier - first CPT code - fourth modifier
MOD13 -
MOD12 -
MOD11 -
CPT2 - AMA treatment code 2
CPTNAME1 - AMA treatment code 2 natural language descriptor
MOD25 - CPT modifier - 2nd CPT code - fifth modifier
MOD24 - CPT modifier - 2nd CPT code - fourth modifier
MOD23 -
MOD22 -
MOD21 -
CPT3 - AMA treatment code 3
CPTNAME3 - AMA treatment code 3 natural language descriptor
MOD35 - CPT modifier - 3rd CPT code - fifth modifier
MOD34 - CPT modifier - 3rd CPT code - fourth modifier
MOD33 -
MOD32 -
MOD31 -
CPT4 - AMA treatment code 4
CPTNAME4 - AMA treatment code 4 natural language descriptor
MOD45 - CPT modifier - 4th CPT code - fifth modifier
MOD44 - CPT modifier - 4th CPT code - fourth modifier
MOD43 -
MOD42 -
MOD41 -
CPT5 - AMA treatment code 5
CPTNAME5 - AMA treatment code 5 natural language descriptor
MOD55 - CPT modifier - 5th CPT code - fifth modifier
MOD54 - CPT modifier - 5th CPT code - fourth modifier
MOD53 -
MOD52 -
MOD51 -
CPT6 - AMA treatment code 6
CPTNAME5 - AMA treatment code 6 natural language descriptor
MOD65 - CPT modifier - 6th CPT code - fifth modifier
MOD64 - CPT modifier - 6th CPT code - fourth modifier
MOD63 -
MOD62 -
MOD61 -
RSDATE - record creation date
TEAM - operative team
RRC - Residency Review Committee Code
PROC - procedures
OPN - diagnoses
PENO - patient encounter number
LAST - last name
STREET -
CITY -
STATE -
ZIP -
PHONE -
DOB - date of birth
SSNO - Social Security number
POSID - point of service ID#
JOB - Misc Notes
REVNAME - Record Reviewer name
REVDATE - Record Review date
BSITE - medical center
PTLOC - In vs Out patient

trdb - clinical findings table
----------
TID - record id number
MRN - medical record number
ADMNUM - admission number
LAST - last name
FIRST - first name
ADM - admission date
WT - weight
RM - Room
BED - bed
HDN - hospital day number
DC - discharge date
YR - patient age
DOC - doctors - initials eg KM/NM/JE
TAB1 - In vs Out patient
HXPX - History and Physical Exam (H&P)
PROG - Progress notes
DX - diagnoses
ICD - International Codes of Disease
OPS - treatments
LAB - laboratory data
COMP - complications
GRAPHICS - images
TRDX - trauma diagnoses
BPIL - Brachial Plexus injury grid
BPI - Brachial plexus injury descriptors
SCIL - Spinal Cord Injury grid
SCI - Spinal Cord Injury descriptors
ISS - Injury Severity Scores
CDX - Cold Orthopaedic diagnoses
SDX - Type in Diagnoses
DEMO - Demographics
SRX - Type in Treatments
PSS - Primary Survey
PHS - Prehospital Phase

[BliarOut]

Told you I needed help

The idea is to be able to statistically analyse trauma injuries to see what really works. You need a lot of information to spot small differences in treatment. It's being driven by a surgeon rather than an IT type, so he knows what he needs to know.

[BliarOut]

What do *I* want from the data?

Here is an example:

Producing the "rrc" equivalent (residency review committtee data) which tracks the surgical experience of surgical trainees.

This will use the "proc" (procedure) table and look at and count up the various RRC classes of surgical experience and then magically make this appear in spreadsheet format.

If you go to www.hwbf.org:800/proc/rrc_tdate.htm
and apply the dates 1/1/1990 - 1/1/2004 you'll see an example of what is wanted. (I'll worry about the PHP to display it in a while!)

[BliarOut]

I am getting the hang of this

OK, to do what I wanted above, I will need to extract and count all the RRC values from the proc table...

So I can use

SELECT COUNT(*) RRCode FROM proc WHERE RRC LIKE 'TA5'

That tells me there are three records in the proc table with the value TA5

That doesn't mean a lot, as we don't know what TA5 really is.

So.... I would imagine I create a table RRCValues which would be

RRCID contains TA1 TA2 etc

RRCDescription A textual description of the RRC Code.

Now, how do I ammend the above statement to link the text from RRCValues table to the proc table?

[BliarOut]

Thank you very much... Slowly but surely, it's taking shape!

[BliarOut]

Next

I want to count the occurrences of a value in proc.rrc. There are 56 possible codes. The query I have kinda does what I want, but needs refining. It counts, but (correctly I assume) ignores null values. I would like to ammend what I have to count nulls as zeros AND then save that value so I can display it in a web page. Does anyone know how I could modify the following?

SELECT RRC, COUNT( * )
FROM proc
WHERE RRC = 'TA1'
OR RRC = 'TA2'
OR RRC = 'TA3'
OR RRC = 'TA4'
OR RRC = 'TA5'
OR RRC = 'TA6'
OR RRC = 'TA7'
OR RRC = 'TA8'
OR RRC = 'TA9'
OR RRC = 'TA10'
OR RRC = 'TA11'
OR RRC = 'TA12'
OR RRC = 'TA13'
OR RRC = 'TA14'
OR RRC = 'TC1'
OR RRC = 'TC2'
OR RRC = 'TC3'
OR RRC = 'TC4'
OR RRC = 'TC5'
OR RRC = 'TC6'
OR RRC = 'TC7'
OR RRC = 'TC8'
OR RRC = 'TC9'
OR RRC = 'TC10'
OR RRC = 'TC11'
OR RRC = 'TC12'
OR RRC = 'TC13'
OR RRC = 'TC14'
OR RRC = 'OA1'
OR RRC = 'OA2'
OR RRC = 'OA3'
OR RRC = 'OA4'
OR RRC = 'OA5'
OR RRC = 'OA6'
OR RRC = 'OA7'
OR RRC = 'OA8'
OR RRC = 'OA9'
OR RRC = 'OA10'
OR RRC = 'OA11'
OR RRC = 'OA12'
OR RRC = 'OA13'
OR RRC = 'OA14'
OR RRC = 'OC1'
OR RRC = 'OC2'
OR RRC = 'OC3'
OR RRC = 'OC4'
OR RRC = 'OC5'
OR RRC = 'OC6'
OR RRC = 'OC7'
OR RRC = 'OC8'
OR RRC = 'OC9'
OR RRC = 'OC10'
OR RRC = 'OC11'
OR RRC = 'OC12'
OR RRC = 'OC13'
OR RRC = 'OC14'
GROUP BY rrc LIMIT 0 , 58