Ecto field references multiple tables?

Phoenix Forum Questions / Help
,
1

Problem
I want to design a table that can be split into multiple tables.

Potential Solution 1
One proposed solution is to split the parent table into child tables

so the parent table have a field called child_id

# priv/repo/migrations/20220824063445_create_parents.exs
...
    add :child_id references([:x_children, :y_children, :z_children])
...

is that possible?

— OR —

should I make multiple fields like:

Potential Solution 2

# priv/repo/migrations/20220824063445_create_parents.exs
...
    add :x_child_id references(:x_children)
    add :y_child_id references(:y_children)
    add :z_child_id references(:z_children)
...
2

Can you elaborate a bit about the kind of data that you have and how you want to represent it? I’m having difficulty understanding what you are proposing. Parents don’t usually point to children, rather children point to parents because a parent can have many children.

3

I have the parent is the item, and the child is the property, the property can either be :string, :integer, etc.

4

It looks like you’re trying to build a polymorphic relationship. Your first solution won’t work because databases won’t allow a single column to be a foreign key to multiple tables (at least the dbs I’ve used).

Your second solution would work. There are a few other ways you can do it and you can read about them here. There are other methods too if you look up polymorphic data modelling you can really dive into it, if you are inclined to.

2 Likes