Relationships between multiple tables

[Thorson]

My database is currently set up with a relationships between two different
tables with referential integrity. In the past all the animals we have were
listed in tblBirthInformation, many tables were linked to this (the animal
was to first be listed in tblBirthInformation); however now we have animals
that were not born here therefore to put them in tblBirthInformation would
create many blank fields in the records. We created another table
(tblCustomFeedAnimals) for the animals we have but are not owned.

Is there anyway to set up a relationship between three tables that to be
entered in the first table the animal must first be listed in either
tblBirthInformation or tblCustomFeedAnimals?

 

[Beetle]

This may be a case for sub-typing, wherein you would create one table
with the fields (attributes) that all animals have in common, with
a field to indicate if you own the animal or not. It would have
a PK field of, for example, AnimalID (Autonumber). You would then
create another table with the attributes that apply only to the animals
you own, with a PK field of, say, OwnershipID (this field would need
to be Number data type, it cannot be AutoNumber). These tables would
be related One-to-One, with the ownership table being the child.

Your data entry form would manage the key values, and you could
also take steps to prevent data from being entered in the ownership table
if the animal in question is not one that you own.

One-to-One relationships are not common and are usually used for
sub-typing like this. I can't say for sure that's what you need, but it
sounds like it based on your post.

 

[Thorson]

That would work, and we did discuss that.

We were hoping there was one way to link the one table to the other tables
(tblBirthInformation and tblCustomFeedAnimals) and requiring that the animal
first be in either the tblBirthInformation or tblCustomFeedAnimals before
entered into the linked table.

The reason we would prefer the later solution is that it would make entry
easier since we work from an excel spreadsheet and we already have the tables
estabilished. If your suggestion is the only option then that's fine it will
just take some time to restructure.

 

[Beetle]

It may not be the *only * option, but i think taking the time now to
create a properly normalized structure for your data tables will save
you time - and headaches - in the long run.

Trying to manage it the other way would require extra work to
handle animal data in two separate tables, making sure data didn't
get duplicated or two animals end up with the same PK value, etc.

 

[Thorson]

Actually two seperate tables would make more sense for our databse, it would
make it easier for our users to enter data. We will try moving the tables
around and restructuring, thanks for the help.

 

[Thorson]

I think it will work out fine, I made a table that has only one field, the
field is every animal's EarTag. I will set this up for referential
integrity, I will require that users first enter the ear tag in this table
before any where else.

Thanks