I think your new solution violates your previous this:
Stepping back, why would you want an Animal module (or struct type)? Why would you need it as a DB table?
Do you want to list all animals and create a list of all Owners? If so, that makes sense.
If you want to create owned animals like owner |> own_new_animal(attrs) no matter what type of animals, then why would you need concrete tables for each types of animals? Are there unique fields that each animals should have? Then it makes sense.
The problem is, being polymorphic and having unique things at the same time is not a easy thing to dodge. But it can be done by trading-off some aspects.
I’ll try to list pros and cons of each solutions (including some not yet appearing in this thread):
Single Table Inheritance
This is when you have a big table (Animal) and each concrete animal tables are a sub-division of that table. You won’t have multiple tables.
pros
– you will only have one table.
– fetching all animals are very easy
– it can be easily implemented in Ecto, because all concrete animal modules can have schemas different with each other but referring to the same big table.
cons
– if you want unique fields for each types of animals, you get NULL columns everywhere (but you don’t care when using the concrete modules’ schema: it doesn’t appear.)
Class Table Inheritance
This is when you have a base table (= Animal), and each concrete animal tables have a reference to the base table plus their unique fields. (Animal {id: 1, birth: "20180101", vaccinated: true} Animal {id: 2, birth: "20111225", vaccinated: false / Cat {animal_id: 1, colour: "brown"} Snake {animal_id: 2, length: 150})
pros
– listing all animals are quite easy, if you only need the basic informations
– no NULL columns
cons
– while getting the full information (of an animal) from the concrete modules are quite easy (you just JOIN :animal), getting the full info from the base side is not (you cannot know what to JOIN).
– This is unless you write some kind of logic similar to polymorphic tables in Rails (animal_type and animal_id), which are mentioned “not preferable” in the Ecto docs.
Concrete Table Inheritance
No tables for Animals will be created. Each concrete animal tables will have all basic informations + their unique informations. This will be good if you want to assure that all animals are animals, but you don’t work with Animals directly.
(I’d rather create a protocol named Animal rather that doing this)
pros
– no need to JOIN when fetching a concrete animal
– works good with Ecto’s abstract tables (Animal will be an abstract table)
cons
– you need to be careful at migrations not to break the entire inheritance
– unique assertions must be done completely on application logic
– especially ids must be unique across all animal tables, so using :binary_ids is preferred.
– any search using keys in Animal should go through all tables (ugh)
Abstract tables
This is described in the Ecto docs. (Sample repo) Using only this seems not very helpful on your usage, but if you are looking at STI or CTI, it may help the implementation.
When you create a Class Table Inheritance, each table refers to an Animal base table. Using abstract tables, you can split that into multiple base tables for each concrete animal tables, for example a cat table will refer tocat_base and snake to snake_base, where cat_base and snake_base will have same columns. Then we will create an abstract table animal, and when you cat |> Animal.add_base_animal_info() it will create a cat_base.
pros
– you can retrieve concrete animal from Animals
cons
– you need to JOIN every concrete *_base tables to do list_animals
I think this is somewhere between Class Table Inheritance and Concrete Table Inheritance.
Embedding data
Embedding can be done in Postgres and MongoDB etc. You can have a single animal table with a field that accepts a map (field :details, :map). Then you define many concrete animal modules which its schema refers to that animal table, having a embeds_one :details, CatDetails where you define a embed_schema for CatDetails. (This example is STI with Embeds)
pros
– tables will be very clean
– no NULLs
– listing all animals is easy
– storing data using the concrete animals’ schema will validate the shape of maps you pass
cons
– the shape of the maps inside :details cannot be verified in DB level
– searching for fields inside embeds may not be good (depends on what db you use)
Hope it helps;
