Issues with relationships - Newbie question (1 Viewer)

Yiannis_cy

Registered User.
Local time
Today, 09:56
Joined
Jul 29, 2010
Messages
16
Guys....i need help

I am building a database for a doctor and i have the following tables

- Basic Patient information
- Patient's mother history
- Patient's father history
- Patient's brother history
- Patient's sister history
- Patient's children history

I use the above tables because the doctor wants to complete a form asking the patient if he had any previous problems in the family and who had the problems.

My problem is that i am confused on building the relationships between these tables.

can someone help me as to the primary and foreign keys i need to use and also how to build the relationships.

As a newbie i will need detailed steps :)

thank you people
 

PNGBill

Win10 Office Pro 2016
Local time
Tomorrow, 05:56
Joined
Jul 15, 2008
Messages
2,271
You wouldn't need all these tables. Just two maximum.

If you use two, One is your Patient Table and the other is the Relatives Table.

Relatives Table will have Primary Key field and the next field would be the PatientID field where you will enter the PatientID - this will link the two tables and allow you to query the records.
Relationship will be One Patient to Many Relatives.

Anther field in the Relatives table will simply be Relationship and hold values like "Mother", "Father", "Brother" etc.

Basic advice only as your database will be much more complicated then just this issue.
 

YNWA

Registered User.
Local time
Today, 16:56
Joined
Jun 2, 2009
Messages
905
PNGBill is spot on.

2 tables.

patient with

patientID (PK and autonumber)
name
dob
address
tel
etc...

relatives table with

relativesID (PK and autonumber)
patientID (FK and set data type to number)
name
dob
address
tel
relationshipID (set data type to Number) (this would be the drop down on form and would be linked to a relationship table with all your relationships in (i.e brother, dad, step parent etc..)
etc...

You would therefore in theory have 2 data tables (patient and relative) and 1 lookup table (relationshiptopatient).

The lookup table would consist of ID (PK and autonumber) and relationship (text)

On form combo box (say cboRelationship) with bound column 1, number of columns 2. size of colums 0cm;2.5cm

Link patient tables relationshipID to ID in relationship table.

Link patientID in patient table to patientID in relative table.

Enforce ref. int. and cascade update/delete.
 

Yiannis_cy

Registered User.
Local time
Today, 09:56
Joined
Jul 29, 2010
Messages
16
you guys are amazing

the only reason i created so many tables is because each member of the family might have experienced more than one problems for example

- cardiac arrest
- stroke
- etc

so instead of having a big relatives table including fields like FatherCardiaArrest, FatherCardiacArrestAge, BortherCardiacArrest etc i split the tables

i forgot to mention this before that there might be many options for every relative

does this change anything?
 

Yiannis_cy

Registered User.
Local time
Today, 09:56
Joined
Jul 29, 2010
Messages
16
just to add something

what happens in the case the the patient has 2 relatives that had a problem?
 

YNWA

Registered User.
Local time
Today, 16:56
Joined
Jun 2, 2009
Messages
905
If a 1 member of family has more than 1 problem, then you might be better using multiple combo boxs (eg. problem 1, problem 2) each with the same lookup table of problems.

Or you could use a lot of check boxes. All depends how big the list of problems can be.

As this way you still selecting the relationship which you want, then your selecting the problems identified for that relative.

If you then need to add another relative you do so under the same patient, and then select their conditions.

Not sure if there is a better way to do it?

Best way I like to do this type of form is main form simple patient, then sub for either below it or create a tab form and have the sub form on the 2nd tab. So you then have 2 tabs to click through, 1 is patient details other is relative details. The subform would be a datasheet or continuous form.

I think I covered the 2 relatives had a problem.

You just select the relative you need in the subform and enter problems, then add new relative, select relationship from combo, then select their problems and so on.

The subform will need designing in a way that its easy for the user to use, datasheet might be best but if your having lots of combos or checkboxes then maybe continuous form. Depends how many of each you need.
 

Yiannis_cy

Registered User.
Local time
Today, 09:56
Joined
Jul 29, 2010
Messages
16
ok i think there is a mistake in the above instructions

i should link:

Relative tables relationshipID to ID in relationship table.

patientID in patient table to patientID in relative table.

i did this and i created a combo box with the information coming from the relationship table and to be stored in the relationshiptype field in the relatives table

when i do this the combo box works but i cant make any selections. in other words the relationships are not clickable
 

YNWA

Registered User.
Local time
Today, 16:56
Joined
Jun 2, 2009
Messages
905
Yes relationshipID of relatives table to ID in relationship table.

Have you created the relationshipID field in patient table and changed the data type to Number?

Then the relationship table has 2 fields ID (autonumber and PK) and relationship?

Then go to form and create a combo using wizard and this will guide you through it step by step.

You need bound column set to 1
Then colum widths should be 0cm; 2.5cm (of however big you need second column)

The control source of the combo box should be pointing at the relationshipID in the relatives table.
 

Yiannis_cy

Registered User.
Local time
Today, 09:56
Joined
Jul 29, 2010
Messages
16
guys im stuck and i dont know why. i am attaching the pic of my relationships which might help


Patient detaisl table
--------------------
PatientID (Unique & Autonumber)
DOB (date)
Firstname (text)
Lastname (text)
Telephone (number)

Relatives details table
---------------------
RelativesID (Unique & Autonumber)
PatientID (Number)
RelationshipID (Number)
Relationshiptype (text)
PCI (YES/NO)
SCD (YES/NO)
MI (YES/NO)

Relatioships table
-----------------
ID (unique & autonumber)
Relationship (text)

i try to create a combo with the wizard using the following

Combo to look up the values in a table or a query
I select the relationships column from the relatioships table
I dont do any sorting
Hide the key column
Store that value in: Here i have options only the fields from the Patient detais table. The other 2 tables are not there to choose from
 

YNWA

Registered User.
Local time
Today, 16:56
Joined
Jun 2, 2009
Messages
905
You need the relative details in a sub form and link that subform to the relatives table/query.

You can then select from any field in relatives table, select the relationshipID field.

What is relationshiptype field? How does this get filled in? The table should be storing the ID only (eg. 1, 2, 3 ...)

You can then pull the relationship types in a query.
 

YNWA

Registered User.
Local time
Today, 16:56
Joined
Jun 2, 2009
Messages
905
The record source of your subform should be the relatives table/query.

linked to the main form via patientID and patientID.
 

vbaInet

AWF VIP
Local time
Today, 16:56
Joined
Jan 22, 2010
Messages
26,374
I noticed in your first post you mentioned "history of each family member" and in your second post you mentioned "each member might have experienced cardiac arrest, stroke, etc". What you've got so far is just the barebones. This needs to be normalised further.

So you need a Relatives Problems table which could look like:
---------------------
RelativeID (PK - Composite, Number datatype)
ProblemDate (PK - Composite, Date/Time datatype)
IllnessID (PK - Composite, Number or Text datatype)
ProblemNotes (Text field)

The reason why I've suggested composite primary keys is because a relative could have more than one illness on the same but you obviously wouldn't record the same illness twice on the same day. If you don't want to have composite keys, which you really should give a second thought, then just have a RelativeProblemID as an autonumber which would be the PK.

The IllnessID would be linked to another table which would hold all possible Illnesses:
----------------------
IllnessID (PK - autonumber OR if Text then maybe a 4 letter unique ID)
Illness (Text - description of illness)

The possible illnesses being Cardiac Arrest, Stroke etc

The relationships would be:
RelativeID: Relatives Details -> Relatives Problems
IllnessID: Relatives Problems -> Illnesses table

Unfortunately, I don't do databases for Doctors so I wouldn't be able to advice you on what extra fields you might need in there :)

By the way, what are PCI, SCD and MI?
 

Yiannis_cy

Registered User.
Local time
Today, 09:56
Joined
Jul 29, 2010
Messages
16
guys thank you for all your help you really put effort in helping me and i appreciate it.

vbaInet i will try your way as well and see how it words out

as for your questions i am not sure about PCI and MI but i know that SCD stands for sudden cardiac arrest.

just to add on my question above let say that the patient had a stroke history when he first visited. then after 6 months he had another stroke. how would i record that?the same problem exists with all the illnesses because each patient can have many diseases which in turn might be repetative
 

vbaInet

AWF VIP
Local time
Today, 16:56
Joined
Jan 22, 2010
Messages
26,374
Reread my last post, the answer is within. Repetition in Relatives Problems table is not an issue, the uniqueness is a combination of all three fields if you made them composite keys.
 

Yiannis_cy

Registered User.
Local time
Today, 09:56
Joined
Jul 29, 2010
Messages
16
Ok mate i have tried all the options tha were given to me and i thank you all.

I have created the following relationships which might look complicated but i think they make sense



Patient Basic information is self explanatory. I have linked that to the Patient history table which contains the details of the problems that the patient had in the past. The connection was done through the FK (Patient ID) which is a number. The 3 tables next to it are reference tables containing the details for each illness.

The relatives table contains the details of the relatives history and it is connected to 3 reference tables that have all the details.

When i create a form everything works but for some reason that apparently i dont understand i have to save the record before being able to select or complete any of the information located in the tables other than the Patient Basic information

can you please help me understand why this happens and what i need to do?

p.s i was able to construct this relationships because of your help and i appreciate it

thank you
 

vbaInet

AWF VIP
Local time
Today, 16:56
Joined
Jan 22, 2010
Messages
26,374
Ok, so this looks completely different from what was advised. Can you give a short explanation the rationale behind this?

I thought PCI, MI etc were all illnesses and what I was trying to get to you was to put all the illnesses as records in one table, not as fields but as records.
 

Users who are viewing this thread

Top Bottom