Re: referential integrity between elements of an array and another table?

Rob Sargent <robjsargent@xxxxxxxxx> · Wed, 19 Apr 2017 13:24:09 -0600



    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).