My new site will allow for 'normal' registration or the use of Facebook openauth.
I have many tables that link data to a user_id.
Imagine one of my users with information on my site forgets they used Facebook to create an account. They create a second 'normal' account and use the site for a while. So the user has data associated with two user_ids.
I want to give them the option of "importing" the data associated with the first account into the newest account.
This is messy, and I would rather design things right in the first place but I can't wrap my head around an elegant way to do this.
Here are the options I have thought of:
Allow a user profile to have a master(parent?)_id, and write all of my db queries to select many user_ids. Upon a merge from one account to another, the losing account gets the winning account's user_id as it's parent_id.
The above, but cleaner: consolidate many logins into a lookup table
System-wide one time update: Find all data with user_id 1 and change it to user_id 2. This gets progressively harder as we store more data.
Merge with data loss- same as #3 but only crucial data. The user loses historical records etc.
Or am I glossing over an easier way to handle the scenario I am trying to plan for?