2

I was wondering whether it is cleaner or faster using arrays of integers instead of creating an extra new table in a m-to-m relationship and using foreign keys to ensure data integrity. What do you think? Do you say using arrays instead is a no-go?

Improve this question
1
  • 1
    The bridge table mechanism has been elaborated and optimized for decades. Just use it correctly. It is by far easier to query as well. Commented Dec 16, 2022 at 8:47

1 Answer 1

Reset to default
2

Arrays to implement an m-to-n relationship are a no-go.

  1. Write down the join between the two tables. You will notice that instead of two joins with an = as join condition, you now have a single join with @> as join condition (or a LATERAL unnest of the array, which is worse). That means that you are reduced to nested loop joins which will be slow if the tables are big (even if you create a GIN index to support the @>).

  2. You cannot have referential integrity (foreign key constraints) that way.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

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

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.