On 04/19/2017 01:13 PM, Henry M wrote:
> I was just reading this question on reddit (the text duplicated
> below). I was wondering if there is an approach for handling array
> foreign key references. I am interested in the answer since I started
> using array fields as well. Thank you.
>
>
> ----- below is the message from the reddit poster:
>
> https://www.reddit.com/r/PostgreSQL/comments/66a74t/question_referential_integrity_between_elements/
>
>
> First off, thanks for taking the time. I don't see any "weekly help
> threads" or anything so I hope it's okay I made a post.
>
> /The Problem/
>
> I'm extending our CRM database in order to manage the ISO
> documentation of the company for which I work. We need, as part of
> ISO, to keep track of project development meetings and attendees.
>
> Currently, I have a "meetings" table with an attendee list as an array
> of integers, each element corresponding to the id field in the
> "person" table.
>
> How do I enforce referential integrity between elements of this array
> and the id field in "person"? All I could find was a proposed change
> for 9.2 or 9.3 that got shot down...
>
> Please help. I don't want to do a join table.
>
Unless you have thousands of attendees per meeting and everyone in
multiple meetings per day I would encourage a join table. What's your
hesitation for doing so? Note I'm an array user myself, so not against
arrays per se, but there should be a justification for denormalizing
(depending your definition thereof).