creating a table to track connections

[Amanda Byrne]

I used the term connections instead of relationships, hoping to avoid
relational database confusion.

I have an Animal Records table that has unique IDs for each animal. I want
to create an Animal Relationships table that will track the type of
connection between the two animals: Mate, Sibling, Cage Mate, Sire, Dam,
Offspring

I initially started the table with a primary key Relationship ID, a text
field for Relationship Type, Animal ID 1, and Animal ID 2, but having
problems figuring out how I will get this to work in the form.

Does someone have a good reference for creating this type of situation?

Thanks, Amanda

 

[KARL DEWEY]

Isn't mate the same as Sire or Dame without sex designation?

I see three tables - Animal, Relations, AnimalRelate. Create a one-to-many
relation between the AnimalID of the Animal table and the AnimalRelate table.
Also the same from Relations to AnimalRelate.

Use a form/subform to show all related animals. In the subform use listbox
to pick related animal and type of relation.

 

[Amanda Byrne]

Mate is actually different than Sire or Dam- "Mate" is "spouse", "Sire" is
Father, "Dam" is Mother

but anyway,

As I don't ever anticipate having additional relationships being necessary
other than the ones listed, I think I would be fine using a Value list in my
row source, as opposed to creating a third table.

The hangups come in the relationship table when I'm querying 2 fields for
Animal IDs (animal ID 1 and 2), was hoping that someone could suggest a
reference site for this kind of setup.

- A

 

[CS]

I'll take a stab at this -- I'm not an Access genius, but I am building a DB
for a vet friend.

It would be helpful to know more about what you are using the DB for, as
your structure often depends on the information you want to get out of the
database.

I think that Karl is probably right about the 3 table set up -- because I'm
assuming that, while, each Animal will have only one dam and one sire, they
may possibly (depending on the species) have many mates and many siblings.
Also, if the animals you are tracking are multi-spousal, or have more than
one offspring in a litter, just tracking "sibling" could mean that they
derived from a certain dam but not the same sire, or a certain sire, and not
the same dam. So, you would need to have a way to track a difference
between simply "siblings" (sharing one or both of their parents) and
"littermates" (sharing both parents).

Since all the animals are listed in tblAnimals, with a unique ID for each,
you *could* have a field in this table for each of the following: Dam,
Sire, Mate, Sibling -- but this would assume that each Animal has only one
of these. If the species is prone to twin or multiple births, and if the
Dam is mated to different Sires, this could be very problematic in terms of
the structure and the real information you want to get out of the DB.

Designing with tblAnimalRelate could allow you to track each animal with
Dam, Sire, Littermate(s), and then you could also search by siblings (any
other offspring from either the Dam/Sire involved), or by direct littermates
(out of the same Dam/Sire). Glad to offer help about how to structure this.

Hope that helps,
CS