Table relationship design

[Joskin]

Hello Gurus,

I need some design advice please.

I have inherited a database with two main tables, tblAnimal and tblHuman,
both have AutoNumber IDs. tblAnimal has 16,000+ records with 24 fields.
tblHuman has 4000+ records with 18 fields.
The tblHuman ID is related to field "Breeder" in tblAnimal, on a 1 to many
relationship because one Human can be the breeder of many Animals.
The tblHuman ID is also related to field "Owner" in tblAnimal, on a 1 to
many relationship because one Human can be the owner of many animals.
So far so good - it all works.

To hopefully improve the breed, a new scheme was introduced to survey the
animals for potential breeding partners. A new table was created,
tblSurvey, again with AutoNumber IDs. This new table currently has 900+
records with 61 fields.

This is where I came in, with very limited knowledge :-(

How should I relate tblSurvey to tblAnimal?

Should I relate the tblAnimal ID to a field called "Animal_ID" in the
tblSurvey?
Or should I relate the tblSurvey ID to a field called "Survey_ID" in the
tblAnimal?
Either way, it looks like a 1 to 1 relationship to my inexperienced
eye, should I be thinking about combining tblAnimal and tblSurvey instead?
Is there an easy way to combine such tables? (what about all those unused
fields?)

Many thanks for reading this far.
And many more thanks if you can help.

Joskin

 

[Beetle]

This new table currently has 900+ records with 61 fields.

Although it's *possible* you could have a table with 61 legitimate
fields, it would be highly unusual and is more likely an indication
that the table is not properly normalized. Take a look at the structure
of the table. If it has repeating attributes (fields) like;

SurveyQuestion1
SurveyQuestion2
SurveyQuestion3
etc.

then it is poorly designed.

As far as relating the tables (assuming correct table design for the
Survey table), it depends on the nature of the relationship. Can one
animal be given more than one survey? Can one survey include more
than one animal?

You might want to have a look at Duane Hookoms example db
"At Your Survey" at the following link. It may give you some insight
on how to proceed with your application.

http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=3

 

[Joskin]

Yes, the tblSurvey is probably poorly designed but that example from Duane
Hookom scared the sh*t out of me - it will take me 6 months to understand it


Many thanks, Beetle.

Rgds,
Joskin