0

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?

2 Answers 2

0

So one possibility:

bird_nests:
bird int FK
nest int FK
rank SMALLINT NOT NULL
UNIQUE KEY (bird, rank)

And then when inserting into bird_nests, all "natal" nests would be assigned rank, e.g. 1 and all other non-natal nests would be assigned an incrementing value per bird

this also has the advantage of allowing for rank to represent a logical ordering (say it represents a "nest history")

of course, it does present some requirement for determining the appropriate "rank" when inserting a non-natal nest into the linking table

0

What do you mean by pivot table? I assume you mean an associative table, for a many-to-many relationship between Bird and Nest. If so, please remove the totally useless ID and use the actual key (bird_id, nest_id) as a primary key.

Unrelated with Normal Forms but definitely a normalization issue in a broader sense, in Options 2 and 3 the bird nest structure is duplicated for natal nests and non-natal nests. In option3 the duplication is more obvious, in option2 the duplication is part of bird. Any additional properties that may be required for a bird nest must be duplicated too.

Based on the initial assumption, something like the following could work.

enter image description here

That is, the existence of a BirdNestNatal means the referenced BirdNest is a natal bird nest.

Alternatively, you may prefer keeping the BirdNestNatal PK minimal.

enter image description here

In both cases

  • Nullable columns are not used.
  • The constraint for a single natal bird nest per bird is enforced through simple uniqueness constraints on BirdNestNatal.BirdId.
  • BirdNest can be expanded with any attribute related to both natal and non-natal bird nests.
  • BirdNestNatal can be expanded with any attributes specific to natal bird nests.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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