Is there a built-in Ecto way to define associations by an array of integers field?

Phoenix Forum Questions / Help
, , ,
1

Hi,

Table A:
Dishes: id and name fields.

Table B:
Users: with a field called favorite_dishes holding an array of id’s (of dishes).

Is there an Ecto way to treat this as an association ? e.g. for pre-loading ?

How to best do that. I am using PostgreSQL.

Thank you

2

In general it is better to use a join table instead of an array of integers. This ensures you can use foreign key references, and will index better. There is no built in support for driving associations via an array of integers. If you’re just doing has many then the associated table should hold the parent id. If you need M:N associations, use a join table.

3 Likes
3

Thanks. Actually, this is only part of the story. There will be a Restaurants table, each serving various dishes. There will be joins across them e.g. user <> dishes he likes <> restaurants serving those dishes. But since PostgreSQL supports those array of integer fields, I thought that could ensure good performane and make it easy to look up the associations.

4

I would definitely start with the “normal” way of modeling this in Postgres which would be via join tables, and then optimize when you’ve got millions of rows and you have specific access patterns you know you need to optimize for.

2 Likes
5

I definitely agree with @benwilson512, not only are you complicating things, but you lose all referential integrity that way.

1 Like
6

We have done this at work and we are in a world of pain because of it.

Don’t do it.

3 Likes
7

What kind of database size we are talking about?

If it’s under 200GB for a table, I wouldn’t even bother with any optimisations besides indexes on most used columns.

2 Likes
8

Do it the boring and the seemingly-inefficient way, run it in production, measure, then think of optimizing it.

Don’t put the cart before the horse.

2 Likes