Database Structure: Reference Column to Multiple Tables?

Ask for Help
1

I’m having some trouble figuring out how to structure a section of my database dealing with mating:

image
image1021×304 13.5 KB

On any given date, we set up one or more groups of bees to mate. Each group is checked every X minutes, and the behaviour of each bee in each group is recorded (ideally, at least… not always possible, which is why Datetime and Behaviour are optional).

The section I’m having difficulty with is the bee table: some bees in each group are female (gynes) and some are male (males). For gynes, I’d like this to be linked to an existing table of gyne information; however, males have no such table. Even if I did make a table for males, you can’t have a reference column that links to multiple different columns.

I feel like I might be overthinking here – anyone got any ideas?

2

Are you saying there will be a reference column in the “bees” table that references an entry in the “gyme” table? Couldn’t this field be left blank for males?

3

why don´t you just leave the field blank if the bee is male? Maybe you can do a rule that if a bee (are we talking about individual bees here?) is male, then the reference cell stays in red (so the person wont try to find a reference in the gynes table)