0

enter image description here

I have 2 birds per pair IE "Maleringnumber"and "FenaleRingNumber" in tblPair If I open a Form for tblBird I want to see all fields in the table AS WELL as "PairedStatus" of the male and the female. The tables in the pic are in a query. How do I link the tblBird and the tblPair

1 Answer 1

2

Your tblPair (Please tell me this is a classroom assignment. I would shutter to think business operational databases had tables names prepended with "tbl"!) is almost a good intersection table which is what is normally used to connect two entities in a relationship like the one you describe.

A "pair" is made up of two Birds, one male and one female. The dates when the pairing was made and ended and the cage the pair are located are tracked.

Here are some of my impressions of your schema. Some are outside the scope of your question, but may affect the ultimate resolution of that question.

You seem to have simplified the schema to include just data that may illustrate the relationship you are asking about. That is good, but there doesn't seem to be any purpose for RingNumber. And RingNumber would appear to be unique so why is there a RingNumberID? Did you simplify away their purpose? So let's just use BirdID for the duration and stipulate that it is an integer.

Let's also simplify the SexID as a simple char(1) field containing either 'M' or 'F'. Here is the new Birds table:

create table Birds(
  ID         int       auto_generate primary key,
  SexID      char( 1 ) check( SexID in( 'F', 'M' )),
  SpecieID   int       not null references Species( ID ),
  AddDate    date      not null, -- Date bird was added to database
  constraint UQ_Birds_FKAnchor unique( ID, SexID, SpecieID )
);

The CageNumber, RemovalDate and RemovalReason are not necessary for this discussion and probably shouldn't be in this table anyway.

What you want is a way to relate a male bird with a female bird of, I assume, the same species. This should be done in such a way to make it impossible to pair two male or two female birds or to include a bird in a new pairing if already in an existing pairing.

This could be done with an intersection table.

create table Pairs(
    MaleID     int  not null
    MaleSex    char( 1 ) check( MaleSex = 'M' ),
    FemaleID   int  not null,
    FemaleSex  char( 1 ) check( FemaleSex = 'F' ),
    SpecieID   int not null,
    Status     char( 1 ),
    PairedDate date default( NOW ),
    CageNumber int  not null,
    constraint UQ_Pairs_Male unique( MaleID ),
    constraint UQ_Pairs_Female unique( FemaleID ),
    constraint FK_Pairs_MaleBird foreign key( MaleID, MaleSex, SpecieID )
      references Birds( ID, SexID, SpecieID ),
    constraint FK_Pairs_FemaleBird foreign key( FemaleID, FemaleSex, SpecieID )
      references Birds( ID, SexID, SpecieID )
);

As you can see, very close to your existing table. You don't show the constraints of your table, and those are critical.

The first field must contain the ID of a bird defined as male ('M') in the Birds table because the second field may only contain an 'M' value. (This is also why there is a unique constraint which includes the ID field even though ID is unique all by itself.) So also with the third and fourth fields defining a female bird. The unique constraints guarantee no bird may be in more than one pairing and each pairing must be with the same species.

It looks like you want to be able to end the pairing, thus making the birds available for other pairing. In that case, add the Status field to the unique constraints. This will make it possible to have the same bird in different relationships but only once for each value of Status. I assume Status is something like "Ongoing" and "SoOverEachOther". But there could be more, like "GettingToKnow", "JustFriends", "HeadOverHeels" and so forth. This works either way.

This table defines a relationship between a male and a female bird of the same species. Each attribute in this table must define the relationship itself. Thus the single species identifier which applies to each bird, the date the pairing took place and the CageNumber identifying the honeymoon suite.

To display each pair, join the Pairs table twice with the Birds table to get the details of each bird. Rather simple really.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.