Table relationship with four 1-1 tables

[rbennion]

hey everyone,



I have a database that is 4 tables existing of a hospital patient's timeline through a rehab program. Table1 is "General" (general info, demographics), table2 is "Intake" (data when they started the program), table3 is "Discharge", table4 is "Follow-up". The PK is the FK in all of these tables because all of the tables refer to the same patient, just at different times in their timeline of care. Do I need to link all tables to each other as 1-1 or is how I have it below sufficient?



Thanks ahead of time.

Rhet

[JoeM]

No, I think that is going to create problems, as if they do not have records in all tables, they will not show up in most of your queries, unless you do a bunch of left-joins.

I think you have a table design issue. If you have multiple tables that are structured similarly, that is usually a sign that they should be combined.
I think Intake, Discharge, and Follow_up should be combined into one table, and just add a new field (named something like "Action"), where you select whether the record it is "Intake", "Discharge", or "Follow_up".

[June7]

Agree with JoeM. Need table for General and its PK will be saved as FK in table for the assessment data. Add a VisitDate to the Assessments table. Use a form/subform arrangement.