3

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:

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

  2. The above, but cleaner: consolidate many logins into a lookup table

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

  4. 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?

Improve this question
3
  • I'd like to ask you to define "This is messy" and explain why it's bad. If you don't want to clarify your question, though, that's okay, too. Commented Oct 1, 2011 at 3:42
  • I probably didn't mean messy - maybe I meant, "my experience with relational databases tells me that there is no elegant solution to the data merge challenge I anticipate, and thus whatever I implement may be mess(ier) than I usually strive for." Commented Oct 1, 2011 at 5:27
  • In my opinion, it's better to update the question than to tack a lot comments onto the question. Rather than explain, could you just fix the question? That would be better. If you don't want to clarify your question, though, that's okay, too. Commented Oct 2, 2011 at 0:25

2 Answers 2

Reset to default
1

Keep all the ids/logins separate and do not merge anything. Instead have a master table that maps the user's relationships. Also add a column to the User table that would point to the master table.

Table MasterUser ID UserID 561 1 561 2 561 3 561 4 Table User ID Email MasterUserID 1 [email protected] 561 2 [email protected] 561 3 [email protected] 561 4 [email protected] 561

If the user logs in with "[email protected]" you can do a lookup using the MasterUserID and quickly see all the IDs that user has.

The tricky part is knowing when to ask the user to associate accounts. I suppose you could go by the current session. But what if the user is on a shared computer? You may want to notify the user via email and give them the option to remove a link if done in error.

Just some quick thoughts on how to merge any user data on the fly. Not sure if this pseudo code would actually pan out

AllUserIDs(@UserID) Declare @MasterUserID = SELECT MasterUserID FROM MasterUser WHERE UserID = @UserID --Return list SELECT UserID FROM MasterUser WHERE ID = @MasterUserID END

You could edit your other queries to look something like

SELECT * FROM TABLE WHERE UserID in (AllUserIDs(@UserID))

Essentially replacing your @UserIDs with AllUserIDs(@UserID)

Improve this answer
1
  • I think you nailed it. I was worried about having to juggle userIds but if I use a method that returns all userIds, code gets much simpler and cleaner. Simple sensible solution, Thanks! Commented Oct 1, 2011 at 5:41
1

I think I answered a question similar to this a while ago, but just can't find the link atm..

What I might do is create a model class without authentication information. Something like (done in Python/SQLAlchemy because that's what I felt like writing at the moment ;)):

User model

class User(Base): __tablename__ = "user" user_id = Column(Integer, primary_key=True, autoincrement=True) fname = Column(String(50), nullable=False) lname = Column(String(50), nullable=False)

User auth model (keeping n authentication methods for each user)

class UserAuth(Base): __tablename__ = "user_auth" auth_id = Column(Integer, primary_key=True, autoincrement=True) user_id = Column(Integer, ForeignKey("user.user_id"), nullable=False) provider = Column(Integer, nullable=False) class AuthFactory(Base): # the index here would match what's stored in the provider column of UserAuth _providers = [ FacebookProvider, MySiteProvider ] def get_provider(self, id): return _providers[id]()

If FacebookProvider and MySiteProvider (and whatever other provider you want to add eventually to your system) implement the same interface, then swapping between providers is trivial.

Note: The above code obviously hasn't been tested and there are many different ways of achieving the same result, this is just what I whipped up off the top of my head. Would love to know of any holes in the idea though as I'm planning on using this concept myself in a future project.

Improve this answer
1
  • I think I'll end up with a solution like this - the challenge I foresaw was when user (Joe) essentially has 2 user_ids from 2 separate signup events - and then wants to merge them. I was hoping I wouldn't have to touch all my other tables, but it looks like I'll be planning to do just that. Commented Oct 1, 2011 at 5:31

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.