Sample Header Ad - 728x90

Handling two different types of relationships between same two tables

0 votes
2 answers
156 views
I'm working on a database where I have two tables where there can be different types of relationships between them, and I'm struggling with the best way to handle it because all the options I seem to come up with have disadvantages. I have a birds table and a nests table. The first relationship a bird can have to a nest is their "natal" relationship (e.g., the nest they were born in). The second relationship a bird can have with a nest is the nests they visit/build as adults. Data is collected over a long enough period of time that some birds will be seen both as nestlings and as adults. Data collection is also not perfect; so some birds may not be observed as nestlings, so their natal nest could be unknown. I have three different schemas I've come up with so far: enter image description here Option 1: Seems the cleanest. It uses a pivot table with an attribute to describe an observed relationship. It prevents null values which, is nice, but it does not have a way to enforce a single natal nest without extra logic somewhere. Option 2: The pivot table just represents the adult-nest relationship. The natal relationship is represented using a 1-1 relationship with a foreign key in the birds table. The downside to this is that this leads to possible nulls in the data. Option 3: Uses different pivot tables to represent the different relationships. It seems to avoid the pitfalls of the other two, but something is nagging me about this being overly complicated. But maybe that is more of a front-end issue than a database issue. In terms of proper database normalization/theory, do any of these options stand out as particularly bad or good? Did I miss an option that will magically solve my problem without downsides?
Asked by anjama (121 rep)
Jan 8, 2024, 05:13 PM
Last activity: Jul 15, 2025, 02:07 PM