Problem linking tables

[AccessNovice]

I am a little confused about how to create the proper relationships between
my tables. I have four tables, Patient information, veterinary information,
tumor information, follow-up information. I am inputing data about a study
done on canine mammary cancer. There is only one patient per veterinarian,
so I would want to create a one-to-one relationship with each table. Every
table has the same primary key - Patient ID. Is this ok? How should I create
the relationship between tables?
Thanks!

 

[John Vinson]

I am a little confused about how to create the proper relationships between
my tables. I have four tables, Patient information, veterinary information,
tumor information, follow-up information. I am inputing data about a study
done on canine mammary cancer. There is only one patient per veterinarian,
so I would want to create a one-to-one relationship with each table. Every
table has the same primary key - Patient ID. Is this ok? How should I create
the relationship between tables?
Thanks!

No. It is certainly not OK!

Each Table should represent instances of one type of Entity. A patient
is one type of entity; a veterinarian is a very different type of
entity. I would suggest that you should NOT use one to one
relationships; maybe right now you don't have one vet with two dogs
under study, but it would be a bit foolish to design the database to
make entry of such a case impossible!

I'd suggest

Veterinarians
VetID Autonumber Primary Key
LastName
FirstName
<other contact information>

Patients
PatientID Primary Key (do you have written study numbers? the
Primary Key should be unique and preferably numeric, but if it's going
to be used by people it may be best NOT to use an autonumber)
<information about the dog, e.g. name, owner, breed, ...>
VetID Long Integer <link to Veterinarians, many side of a one to
many>

You may want to simply keep tumor information in fields in the
Patients table, assuming that each dog only has one condition being
treated; or if an animal might have multiple tumors, you would have a
separate Tumors table:

Tumors
TumorID Primary Key Autonumber
PatientID <link to Patients, many side of a one to many link>

Treatments? I don't know; does each dog get one and only one
treatment, end of story? or might there be multiple treatments over
time?

As for followups, surely there are multiple assessments over time to
see how well the animal is responding to treatment - if so, there's
another one to many!


John W. Vinson[MVP]

 

[AccessNovice]

Thanks for the reply! I added a vet ID and created a one to many relationship
between the vet info table and the patient info table. As for the other two
tables, I think i have to leave it as a one-to-one relationship with the
patient info table (connected through the patient ID field) because there is
only one follow-up per patient and one initial tumor information per patient
(the other two tables). I would have combine the patient info, initial tumor
info, and follow-up info tables into 1 table but i think there would be too
many fields. Is a one-to-one relationship ok in this case? Thanks so much
for the help!!!!

Lindsay

 

[John Vinson]

Thanks for the reply! I added a vet ID and created a one to many relationship
between the vet info table and the patient info table. As for the other two
tables, I think i have to leave it as a one-to-one relationship with the
patient info table (connected through the patient ID field) because there is
only one follow-up per patient and one initial tumor information per patient
(the other two tables). I would have combine the patient info, initial tumor
info, and follow-up info tables into 1 table but i think there would be too
many fields. Is a one-to-one relationship ok in this case? Thanks so much
for the help!!!!

The limit is 255 fields (absurdly high!) or - more often a problem -
2000 bytes *actually occupied*, exclusive of memo or OLE fields.

Are you pushing these limits? One to one relationships are QUITE
uncommon, because usually it is in fact simpler to put all of the
information into one table. Do any of your tumor or follow-up info
fields come in groups (the telltale clue is fieldnames ending in 1, 2,
3 and the like)?

I'd *try* to get it all into one table, if in fact it cannot be
normalized into one or more one-to-many relationships, just for
efficiency (opening one table is less work than opening two). But if
necessary, then yes, one to one relationships will indeed work.

You'll probably want to create a Form based on the patient table with
Subforms based on the tumor and followup tables if you do in fact go
with the three separate tables.


John W. Vinson[MVP]