One-to-many and one-to-one in Elixir Ecto at the same time

Question

I have 2 tables/schemas with relationship one-to-many, let's assume there is a Person that can have multiple Animal. Then each animal has person foreign key to implement this relationship. I use Elixir with Ecto, so it's straightforward for now and I have

schema "persons" do
  ...
  has_many :animals, Animal
end

schema "animals" do
  ...
  belongs_to :person, Person
end

I want to introduce the concept of person's favourite animal and each person can have at most 1 favourite animal (0 or 1). How to implement this second relationship between two tables when there is already one-to-many relationship?

Two alternative solutions come to my mind but neither is flawless.

1. Don't introduce one-to-one, but have another field in Animal named favourite with true or false. This is simple solution, but lacks database enforcement on having at most 1 favourite animal because there is possibility in DB for multiple animals of the same person to have favourite: true so there is a need to keep this constraint manually in business logic and continuously watch for it.

2. Introduce one-to-one relationship. Animal has already person_id_fkey, now I need to add animal_id_fkey to Person which indicates person's favourite animal. Intuition suggests me that person has_one favourite animal, but in Ecto we use belongs_to to introduce foreign key, so it comes as follows.

  ...
  has_many :animals, Animal
  belongs_to :favourite_animal, Animal
end

schema "animals" do
  ...
  belongs_to :person, Person
  has_one :person, Person <- this name must be different due to conflict
end 

Verbs suggest that the person belongs to animal while the reality is quite different. Also there is ambiguity in field names - person has favourite animal, but how should this field has_one Person be named? It's not clear for me as more and more questions arises in the second scenario.

What is the idiomatic way in Ecto (or more generally in SQL) to solve such bi-directional foreign key relationship?

Answer 1

I cannot test it right away, so I’m posting this as an answer for the sake of formatting only. Feel free to downvote if it does not work for you.

Verbs do not lie to you, simply use belongs_to/3 with proper options.

schema "persons" do
  has_many :animals, Animal
  has_one :favorite, Animal
end

schema "animals" do
  belongs_to :person, Person, foreign_key: :owner_id, references: :id
  belongs_to :favorite_of_person, Person, foreign_key: :favorite_of_id, references: :id
end

Answer 2

Have you tried to use embed schemas? you can use embeds_many