Say you were designing a database for a social networking site. People who are registered obviously exist in the database, have a known username, some kind of unique identifier in the database, and a list of known associated email addresses. People who are registered list people they know. Some of those people exist in the system already. That's easy. Link this unique id to that unique id. But there are other people, those who haven't joined yet. They have a known associated email address. But they might have other email addresses that other people know them as. So user A says "I know marty, he's firstname.lastname@example.org", while userb says "I know marty, he's email@example.com", and we have no way of knowing that this is the same person until marty comes to the site, signs up, and says "these email addresses are me". But we need to remember those links. unique id to .. email address. ew.
I don't want to create a Person record for every unknown email address, and then consolidate them later. I dislike that sort of thing. I don't want links to go to email addresses, 'cause that complicates the finding of links.
THO, it occurs to me... I'm using the same column type for a person id as I am for an email_address id. The email addresses are going to be stored anyway, I can just add a flag in the links table to indicate whether it's a known person, basically indicating which table it's pointing into. I'd have to drop the foreign key constraint on that field, but that's ok.
Other obvious solutions that I'm completely missing?
edit: Another option, it occurs, is an unconfirmed-links table (maybe 'pending_links'?), indexed off the email id, so as emails are linked to people, the pending links move to the real links table and are deleted. A table that only holds junk I won't mind deleting stuff from.