1

Let's say there are two separate Person records

  • V. Baker
  • Vincent Baker

each with their own various associations. These could be things like photos, contact information, etc.

And let's say that it turns out these are the same person. What's the best way to figuratively "combine" them so that it's possible to access either record and also pull in the direct and associated content from the other record/s as well? I say "figuratively" because they'll need to remain separate so that each record's unique data will be preserved.

  • Create a one-to-many self-referencing relationship where one Person can have one or more People?

  • Create a separate column with a unique identifier? And when two or more records are deemed to be the same, simply give them all the same key?

  • Or some other technique I don't know about yet?

My concern, though, is that whatever solution I implement, there's a good chance it will need to be transitive. So if A is noted as being the same as B, if a new C comes along and is noted as being the same as B, this should also result in C being noted the same as the original A.

Thank you in advance for any help and insights!

  • 2
    You say that these are found to be the same person, and you also say they'll need to remain as separate records so that "each record's unique data will be preserved". If they are the same person, anything different about the records would be inaccurate, so why would you need to keep both? – Doug Deden Jan 22 at 19:55
  • 1
    Hello @DougDeden! Well it has to do with where the originate from. So A will enter the picture from a different document history than B. And it's important to know how they came into the system. If they "combine", my concern is that origination history will be muddled/lost. Does that help clarify it at all? Did I understand your question properly? – Spectator6 Jan 22 at 20:31
  • 1
    Yes, you understood my question. And your answer helped clarify it. Thanks. – Doug Deden Jan 22 at 21:00
0

Here is a suggestion users_equal_user

create table  user_equal_user (
ueu_user_id int primary key ,
ueu_link_user_id int);


Select * from users where user_id in
(select ueu_link_user_id from user_equal_user where ueu_user_id = pass_in_user_id);

This will provide a many to many relation your looking for and be unlimited

EDITED OP asked how to deal with A=B=C=D to get all the other cross linked ids so here is some untested code that should get you close

create or replace function get_associated_ids(puser_id int,  
                                       puser_id_matched int[] )
 RETURNS table(user_id int, user_id_linked int)
    LANGUAGE 'plpgsql'

    COST 100
    VOLATILE 
AS $BODY$
declare
    _r record;
    _found int;
    _matched int[]; --use to block already processed ids,  
                    --this is here encase  of a
                    --bad entry that creates a circular 
                    --reference that would create an endless 
                    --recursive loop

begin
        _matched = array_append(puser_id_matched, puser_id);
        for _r in Select ueu_user_id, ueu_link_user_id 
                    from user_equal_user 
                    where ueu_user_id = puser_id 
                        and ueu_user_id <> any(puser_id_matched)
                        and ueu_link_user_id <> any(puser_id_matched) ; 

            select ueu_user_id into _found  from user_equal_user 
                     where ueu_user_id = _r.ueu_user_id;
            if found then
                    _matched = array_append(_matched, _found) ;
                    return query ( select * from 
                            get_associated_ids(ueu_user_id, _matched ) );
            end if;
            user_id = _r.ueu_user_id;
            user_id_linked = _r.ueu_link_user_id;
            return next;
        end loop;
end;
$BODY$
  • Hello @zsheep! Would this allow for transitive associations? So if A == B == C == D, to easily collect A, B, and C if "starting at" D? – Spectator6 Jan 22 at 21:23
  • yes one can do this with a recursive function that matches where ever it finds a different ID then return a result set from the function, OR maybe with a recursive CTE statement. I would have to think about how construct a CTE statement.. – zsheep Jan 22 at 21:38
  • Thank you for the update @zsheep! I really appreciate your help! – Spectator6 Jan 23 at 1:12
  • @Spectator6 your welcome. I did not test this code, i'm sure there are bugs and typos in it I just saw two omission declaring the types on the returns table() bit. – zsheep Jan 23 at 1:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.