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!
A
will enter the picture from a different document history thanB
. 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 '20 at 20:31