Table Structure, Any obvious issues?

[Nitesh9999]

Hey i have finally got around to create my actual db, putting into practice what i have learned in these forums over the last couple of weeks.

This is a bit of background on why the db is needed...

What im trying to do is convert our excel spreadsheets to a db. This has the benifits of giving us a wide range of reports at the click of a button, rather than the copying and pasting we currently do in excel.

We receive sheets from different areas (PCT's) telling us what treatments they have carried out. We store these in excel and send them back invoives with a copy of an excel spreadsheet summarizing what they have been billed for. and we send out monthy, yearly summaries.

Here goes on the Table structures

tblPCTCode
-PCTCode_ID-----around 35 codes to distinguish where the treatment was carried out
-PCTCode

tblTreatmentGroup
-TreatmentGroup_ID------ 6 groups a "parent" of the Treatment types below
-TreatmentGroup

tblTreatmentType
-TreatmentType_ID---------34 Treatment types each belong to a Treatment group above
-TreatmentType
-UnitCost--------- A value set at the begging of the financial year, maybe changed, used for calculations
-PlanValue------- Same as unit cost above

tblFinancialMonth
-FinancialMonth_ID
-FinancialMonth

tblPatients-------------------Contains the bulk of the Patients details
-Patient_ID
-PatientNHSNumber
-HospitalNumber
-PatientPostcode
-StartDate
-EndDate
-GPName
-GPPostcode
-Act
-Value
-PCTCode
-TreatmentGroup
-TreatmentType
-UnitCost
-PlanValue
-FinancialMonth

I appreciate any views on the structure. The db is attached with the above structure.

Nitesh

 

[ColinEssex]

I haven't had a chance to see your example yet. But looking quickly at your tables.

1) you don't need to have a financial month table as this can be determined from the patient episode dates.

2) your patient detail table should only contain demographic data.

3) you need a separate table for the patient episodes (linked back to the patient ID)

4) I'd have a GP masterfile and link the episode to that via a GPID code and I think you could have the PCT code in there as well.

Col

 

[Nitesh9999]

Hey Col

Thanks for the reply

Point 1

1) you don't need to have a financial month table as this can be determined from the patient episode dates.

This one seems to be confusing me. The financial month doesn't really relate to the patient in any way. The financial month table will hold just 12 values. 1-12 for each fiscal month. With 1= april etc.

Point 2

2) your patient detail table should only contain demographic data.

The Main table is tblPatients. the other 6 are (ive been told) lookup tables. So the extra fields in the tblPatients would look up values from the other tables.

Point 3

3) you need a separate table for the patient episodes (linked back to the patient ID)

I dont understand what you mean by episode. If you mean treatment type??? We create a new record every time a patients visits regardless of wether they have been to the hospital before or not.

Point 4

4) I'd have a GP masterfile and link the episode to that via a GPID code and I think you could have the PCT code in there as well.

Again the word episode is throwing me (sorry). But we type in the GP's manually because they are always changing and a separate table for them would require alot of maintenance.

HTH you understand what im trying to achieve.

Any more questions i'll be glad to answer because i dont want tio move from the table stage until I am completly happy.

Thanks again

Nitesh

 

[Birdy]

Sorry, but I agree with Colin.

Seperate the Patient information into two tables.
Patient ID to link the tables. 1 showing the Main information like:-
-Patient_ID
-PatientNHSNumber
-HospitalNumber
-PatientPostcode

the other showing the rest of the information as well as the -Patient_ID field.

This is to cut down on the level of duplication.

Also,
tblTreatmentGroup
-TreatmentGroup_ID------ 6 groups a "parent" of the Treatment types below
-TreatmentGroup

tblTreatmentType
-TreatmentType_ID---------34 Treatment types each belong to a Treatment group above
-TreatmentType
-UnitCost--------- A value set at the begging of the financial year, maybe changed, used for calculations
-PlanValue------- Same as unit cost above

In the tblTreatmentType table, you need to show the TreatmentGroup_ID so that you can then link the tables.

The reference to the GP field is valid on both counts.
Count 1. Why type in a name when you can select it.
Count 2. Which locum was used when this GP was off or left.

In my opinion, it would be better to have a table with lots of names in it so that the CORRECT Spelling of the name can be selected than to have to type in a GP's name. Try reporting on a GP when you don't know the correct spelling of their name.

The main point is:-
If you wish to do a report to see which patients are with a particular GP, you will have to trawl through lots of irrelevant data if the GP is not in a table.

Hope this helps.

 

[ColinEssex]

Sorry I thought you were working for the NHS.

An episode is a visit or period of care as a patient.

So you can visit outpatients for an appointment (that is an outpatient episode) or

you can be admitted to a ward then discharged (that is an inpatient episode)


any financial month or week number etc can be calculated from either the episode start date or end date. But if it doesn't relate to the patient in any way. . . . .why do you need it?

Col

 

[Nitesh9999]

Thanks guys.

This is what i want to end up with (attached). I built this to learn how to link combos etc. But obviously maintaining that monster would be a nightmare.

The main table in the attached tblPatients is what i am splitting up. The report "all" is what i need to end up with (type in 5aa in the parameter box to view the report).

If you get a chance to view the attached. Im sure it'll help you understand better.

Above all i need a report exactly like the the report all i attached.

Thanks

Nitesh

 

[Nitesh9999]

Ive taken your advice (seperated tblPatients). You mention trawling through data to find a particular GP. I know that it makes sense but we will never (well we never have *Yet*) query on the GPName field. So the oddspelling would'nt make a difference. But i've given it is own table (Dont think its neccessary, but your the experts ).

The only thing that is needed in the report, are the fields act and Value. These 2 will also be used for calculations on the report. The other data (GPName etc) is needed if one of the hospitals calls us regarding a patient. We would just run a simple query in that case searching for a hospital or NHS number.

I also added the FK of TreatmentGroup_ID to link tblTreatmentType and tblTreatmentGroup


Heres what ive got now

tblPCTCode
-PCTCode_ID PK
-PCTCode

tblTreatmentGroup
-TreatmentGroup_ID PK
-TreatmentGroup

tblTreatmentType
-TreatmentType_ID PK
-TreatmentType
-UnitCost
-PlanValue
-TreatmentGroup_ID FK to tblTreatmentGroup

tblFinancialMonth
-FinancialMonth_ID PK
-FinancialMonth

tblPatients
-Patients_ID PK
-PatientNHSNumber
-HospitalNumber
-PatientPostcode
-Act
-Value

tblDates
-StartDate
-EndDate
-Patients_ID FK to tblPatients

tblGP
-GPName
-GPPostCode
-Patients_ID FK to tblPatients

Thanks

Nitesh

 

[Pat Hartman]

I don't understand the purpose of tblDates. The dates go with the treatment record.

If tblGP represents a doctor, you need a separate table to define doctors and their contact info. Then you need a relation table to relate doctors to patients. You may need this to be date sensitive so that each time a patient gets treated, he might have a different set of doctors.

 

[Nitesh9999]

Hey Pat

When you say Dates should be with treatment record, which table? tblTreatmentTypes, tblTreatmentGroups or tblPatients (Where i think it should go).

And also isnt the GP data in a table of its own already?

Sorry i'm still getting to grasps with this normalization game

Thanks

Nitesh

 

[Pat Hartman]

Currently the GPs table has in it a foreign key that references the patient table. That means that a GP can only have a single patient. I like those odds but that isn't reality. In reality a GP will have many patients. If you restrict a patient to a single GP, you could put the GPID in the patient's record. But in any event, the patient's ID must be removed from the GP record. To me GP means General Practitioner. Are you using that to mean PCP (primary care provider)?

When assigning attributes to entities - think about how many times an attribute occurs for a single instance of an entity. So, are dates an attribute of treatment type - no, they have nothing to do with the type of treatment. Are dates an attribute of treatment group - no, they have nothing to do with the treatment group. Are dates an attribute of patient - we're getting warm but there will be more than one set of dates associated with a patient and we can only store a single set of dates in any row so there must be some other entity we are missing. There should be a treatment entity. This entity defines an instance of treatment and it contains patientID, treatmentID, GPID, and yes! dates.

Take a look at a book called "Grover Park George on Access". It has an excellent section on normalization. The best I've seen in recent years.

 

[Nitesh9999]

Your right Pat GP is General Practitioner.

Ive been on another forum (still trying there too) where the author of that book is an active, and he couldnt help me (yet). Ive read through tonnes of material on Normalization, different theories, but im still lost.


If you look at "Copy of Working xtab parameter 050405.zip" above and look at the main table tblPatients, that is what i'm trying to break down. I need no more or less data than there is in that table. Maybe looking at that will help you see wht im trying to end up with. then if you look at the report "all" and type in 5aa in the parameter box. it will bring up what i need as an end product.

HTH you understand this a little better

Nitesh

 

[Nitesh9999]

Please let it be right, PLEASE!!!!

Here it is

If this aint right I have no idea what to do...

Data redundancy - i cant see any.

All other things please query me on. but make sure you ask in laymans terms. I think alot of the trouble i've had has been communicating my problem across to you guys.



Thanks

Nitesh

 

[Nitesh9999]

Ah, i spotted an error, and changed a field type.

But this is it now, i hope.

Suggestions/Views welcome...

Thanks

Nitesh