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

[PetrolTed]

Mail me and I'll help if I can.

[BliarOut]

Thanks Ted,

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

[docevi1]

you can ask me as well, I might not be able to help but have a network of techie friends whom we all help out

[pmanson]

docevi1 said:

you can ask me as well, I might not be able to help but have a network of techie friends whom we all help out

Same here. Do a bit of SQL when i'm at uni. I'm sure if I can't do it I can find someone who can.

[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.

[pmanson]

Any chance you can list what you need to record?

eg. Hospital(Doc_id, Doc_name, Patient_Id, Patient_Name, Operation_id, Operation_Details)

Easiest way would be to normalise it like you were doing a new project, then work out how to re enter the existing data.

Otherwise you'll run into issues in the future.


Post it up and i'll give it a go.

[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.

[docevi1]

normalising simply means taking out reduntant information, so for instance a table x doesn't contain multiple lines of same info:

table x:
Name, Street, House number...
Stefan, Main Road, 24...
Gordon, Main Road, 24...
Jeremy, South Street, 56

What you'd do is have a table for address with it's own primary key (a unique identifier, usually an autonumber) and list it instead:

table y:
Name, AddressID
Stefan, 1
Gordon, 1
Jeremy, 2

table address:
ID, street, housenumber...
1, Main Road, 24
2, South Street, 56
3, North Circular, 24

What it means is that since you aren't copying the information to multiple entries it's (a) easier to update and keep relevant, (b) less chance for mistakes to be made upon entry.

*****
An example would be my little picture scripts, in my mySQL db I have:

albums(id, name, type, folder, title, description)
files(id, name, albumID, views)

So I specifiy an album (which dictates where the files are strored - folder & type) in one table, and all the files in another. This means for each picture (some 1800 entries) I'm not copying in the location of where it is stored, this saves me not only space and ensures that it's accessed (marginally) quicker, but more importantly the chance for errors are shortened and if I want to move the images I can do by changing only one record.

Does that make sense?

*****


There are lots of different Normalistion standards, 1st, 2nd and 3rd are the most common (it goes much higher however), essentially they mean:

1st, all data is replicated in each record of one table (i.e. table x)
2nd, data is seperated into tables (i.e. table y & address)
3rd, all transient dependencies are sorted out as well (i.e. making another table to house all the house numbers that are the same)

You need to ensure your tables are in 2nd Normal Form minimum, but 3rd sometimes can take things a bit far IMO


>> Edited by docevi1 on Wednesday 8th September 11:13

[pmanson]

I usually go down to 3rd normal form all Boyce Codd (BCNF) as that is what they teach us at uni.

Oh what fun I have in Databases lol

[plotloss]

Dont get too hung up on normal forms.

I doubt there is a database anywhere in a production environment that is 3NF let alone BCNF or 5NF. Most struggle with 2NF!

Denormalising for performance is probably one of the biggest ills ever foisted on DBA's

[Liszt]

3NF should give you no redundancy but at the cost of performance as Plotloss has alluded to.
Before coming up with your database design, create your entity relationship diagram.

Get a big piece of paper and start scribbling.
You put down objects(entities) such as "doctor" and then group the properties which relate to the object such as name, email address, etc.

Once you have these separated out you can then decide how they link together.

This should then start to lead you to a physical implementation of your logical model.

If you have a think about the entities then, bung them on here, We can slap together a db structure from there, or in practice, one db structure per poster!

[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

[docevi1]

thats some list!

The standard way to describe it is:

cnote(CID, WUTYPE, RDATE, SURG, SECY, MSURG, MSECY, ETXT)

The names should denote what that field is, i.e. I cID is the id for that table...

it'll take a fair while to plow through all of that!

[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.

[plotloss]

If I get a spare hour or two today I'll have a look...

[john_p]

The first thing I'd do is put all the patient data into a single table and refer to them with an ID or similar from the other tables.

Possibly the same with medical staff - you could maybe create a new 'Staff' table with doctors/surgeons/secretaries and their contact details - then a new table to associate Patients with Staff (and perhaps procedures/operations as well?)

Probably the best thing you can do is decide what information *you* need to get from the system and draw it out on a big bit of paper, working out where there are one-to-one, one-to-many and many-to-many relationships between 'entities'. (eg. one doctor can work with many patients, one patient can have many operations etc etc)

What do you need to achieve with the data you have at the moment?

>> Edited by john_p on Thursday 9th September 11:53

[ATG]

Bit off-topic, but ... It's very easy to put together database applications on the Microsoft platform ... the tools are easy to use, loads of people have the skills to put _something_ together, they can easily interact with Excel or whatever for stats analysis.... but .... MS SQL Server comes with a hefty price tag making it inappropriate for small non-commercial projects.

But MSDE is free. MSDE is the Msft Data Engine ... basically, SQL Server without the admin tools and with a limit on database size (and number of concurrent users?). www.simego.net/sqltools.aspx lets you download an MSDE admin and development app. Might be enough to make MSDE a useable alternative to SQL Server for a lot of smaller apps.

P.S. I'm not saying that My SQL isn't a sensible platform for your project. If no-one is saying "you must use technology X", or "we can only support technology Y", then go for it.

[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?