One to One relationship

[MikeFar]

Hi. I have split a large table which was about 150 fields long into 3 tables. The original table included patient details such as address, medications (tick boxes) and health conditions...more tick boxes.
Can I link all tables one to one using the Patient ID number as Primary key in each new table? I tried doing it but I need to manually insert the Patient Id in each table in order to function well!
By the way, the original table was linked as one to many with another table relating to appointment details. Should I link all tables to the Appointment table?
Thanks

 

[plog]

Can I link all tables one to one using the Patient ID number as Primary key in each new table?

You can, but it sounds like you still don't have a proper structure. Just from what you have described, you should have a Patients table, a Medications table, a Health Conditions table and an Appointment table. The relationship between Patients and Medications, Patients and Health Conditions and Patients and Appointments should be one to many. If that's not the case, then most likely you're structure is incorrect.

Whatever the case may be, whenever you want to link tables, you are going to need an ID field and value in all tables that allows that link.

 

[MikeFar]

Thanks for your reply. The fact is that each patient has 1 list of different medication, 1 list of medical conditions but many appointments.
I created a form with patient personal details with their ID as PK. I also created sub forms with medications and health conditions and both have their Patient Id as Pk. It works but only if I enter the patient details first, close the database, re-fetch the patient ID, and only then I can tick which medications and health conditions I need. The Appointment section (One-to-Many) works fine.
I would like to be able to tick the fields without closing and re-opening the database.
Thanks again.

 

[plog]

The fact should be that you have a table that holds multiple medications and a table to hold multiple conditions for a patient. Most likely medications and conditions shouldn't be tick boxes--what happens to your system when you have to add a new medication or condition? You have to restructure your tables and re-layout your form.

With a properly structured one it would be able to easily accomodate new conditions and medications just by adding them to a table.

 

[MikeFar]

Each Patient may be taking 5 or 6 different medications and be suffering from a number of health conditions. If I create a 1 to many table, I would only be able to select one medication and one condition!
Adding a medication or condition is easy. Just add a field in the table, view field list and drag in a new tick box.

 

[plog]

No, you could select many (thus the relationship) medications and conditions.

PatientID, PatientFirstName, PatientLastName
17, John, Smith
23, Bob, Jones


MedicationID, PatientID, MedicationName
1, 17, Aspirin
2, 17, Novocaine
3, 23, Penicillin
4, 17, Viagra
5, 23, Aspirin


One patient to many medications. The same would work with conditions. With this method you don't have to make any structural changes to your database when a new medication is used.

 

[MikeFar]

The conditions table may have 5 or 6 different options per condition. Eg: Mild, moderate, severe, acute ....and I have drop down menus for those.
The thing is that now I have right mouse clicked the joins and deleted the relationships for medications and conditions. Since they have the Primary key common to the Patient table, it works fine!!!
How does it work without a relationship? Ok, I know that if I delete a Patient, his medications will remain in the table but I do not intend to delete any. Could it present a problem in the future?

 

[plog]

Because a condition can have a severity (and all conditions have the same set of severities), that severity needs to be kept with the condition. So your conditions table would look like this:

ConditionID, PatientID, Severity
3, 17, Migraine, Moderate
6, 17, Heart Attack, Acute
7, 23, Migraine, Severe
11, 23, Dementia, Moderate

Without a relationship, if you delete a patient you could have orphaned data in those other tables. This probably isn't a problem, because when generating reports your orphaned data will most likely be ignored since it has no parent to report on.

I try never to delete data, instead, I would make a Yes/No field in the Patients table that designates to not use this record. I would name it something like 'Archive' and then when I go to run a report I would exclude those that had their Archive field checked.

 

[MikeFar]

I am trying to do as you said. Incidentally, yesterday I came across a similar reply and an example is also posted. (Search allenbrowne casu-23)

Somehow, I cannot get the child data to link with the parent. I will have another go this evening.

Interestingly, this guy suggests that Tick box should not be used at all!!!
Thanks

 

[MikeFar]

Done at last! Is a table with 99 fields considered as too large? About 70 of these fields are set as not required and will only be populated in about 1 in 4 records. Will all those blank fields bloat my database?

 

[plog]

Yes to your first question and yes to your second question. It sounds like you didn't implement it the way I suggested.

 

[MikeFar]

I did! The remaining ones (about 70) would require an entry such as a short description. They are not yes or no items. About 30 of them are mesurements of different parts of the anatomy. So I have the name of the anatomical part and a text box to enter the value.
It was close to 220 fields when I first designed it. You suggest that I break it down more?

 

[MikeFar]

While writing the previous post, I realized that I could put all the numerical parts in another table. I copied the original table (with Data) to a new table, linked it, removed the duplicates, new query ....and it works.
Are 40 fields still too much??? I cannot figure out how I can group more fields.

 

[plog]

My guess is yes, 40 is too much. Do you have tables for Conditions and Medication like I suggested? If you can post your structure and perhaps a few fields of sample data I can be of more specific help.

 

[MikeFar]

This is how it is now. Unfortunately, it has plenty of jargon which might not make sense to most people. It is actually planned for a dental clinic so besides the medical aspect, there has to be information about each tooth, occlusion, defects etc.
I am open to all suggestions as this my first real assignment.

 

[gemma-the-husky]

it is hard to know how accurate this all, especially in a "strange" environment to many developers. (medical/dental practice) tblconsent looks strange with all the numbered suffixes

table design sometimes needs refinement. you start doing something, get so far, and then need to resdesign. however, the more you have done, the harder it can get to redesign in some cases

eg - do you need just to know the latest position on the teeth condition, or do you need to be able to reconstruct the teeth condition as at any previous appointment? You need to consider stuff like this now, as part of the initial analysis. Are drugs dispensed associated with the patient, or with the appointment? lots of subtle stuff like this that might not be obvious to a non-experienced developer.

is this for a real business? if so, it might be worth paying someone for a few hours of professional analysis to get these tables set up correctly in the first place. The better the table design, the easier the implementation.

out of interest, a one-to-one relation is generally (but not always) a mistake. one-to-one is only necessary to solve occasional special cases.

 

[MikeFar]

1) The drugs are just a list of medications each patient is on. So it is related to Patient ID and more can be added if the patient turns up and tells the doctor that he is on some new drugs. It's purpose is just to know what treatment can be given and which cannot.
2) The database is not intended as an appointment system. It is just a record keeping system to replace cards and folders system.
3) The clinic is a voluntary organisation. No cash available.
4) Basically it works. The problem is that I am sure that I have to re-design it but I cannot understand what problems I might get if I had to leave it as it is.

I am very new to access and sometimes I find it hard to understand what should be a one to one or many to many!

For instance I want to keep a record of which teeth are present and which are extracted. Teeth are known as tooth 11, 12, 13, 14, 15 etc. So each individual has one tooth no. 11 (One to one) or many patients have their own tooth No. 11! (Many to many)????

I am getting there and I am sure that I can come up with something decent with all the help I am getting.

Thanks for your input.

 

[RainLover]

Mike

If this is your first assignment then you have done well to get this far but it still requires a lot of work design wise.

When I see fields that are Numbered then it automatically rings alarm bells, especially with an application a large as yours. You table design work could take, or should I say will take a lot more work. I am talking Days not Hours.

You need to get this right. If you don't you will have an abundance of issues when it comes to designing Forms and Reports.

Suggest you read up on Normalisation then reread it again till you understand.

The advice you have been given so far is sound, so keep working the way you are going.

Also read up on Naming conventions as this will also help.

 

[MikeFar]

Ok. The numbers are actually the names of the teeth! I tried making different arrangements but that was the only one that worked so the dentist can tick which tooth is present and which is not. Each tick box corresponds to one particular field.
On the consent table, each field supplies info to the consent form which is a report fired by a macro. Each line on the consent form (The printed one) has a corresponding field and some may be left blank, thus 6 lines of remarks etc.

I am uploading a screen shot of the charting tab.

 

[MikeFar]

Say I turn the question 180 degrees so non medical people can understand better.
If a car mechanic wants to print a report on each car he has in his garage for surveying purposes. He makes a one table database with car reg. no, chassis number, engine number and a list of parts like front bumper, back bumper, bonnet, boot, right front door, silencer.....and he just fills in the condition of the parts that need repair. Other parts can be left blank which will mean no repair required.
Forget the date of surveying, owner etc, those will be in another table.
Is there any other way but to put all the different parts in one table? Would he have to sub-divide the table into smaller tables of Body parts, Engine parts, Electricity cables, Upholstery etc?