Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

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?

share|improve this question
    
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. –  S.Lott Oct 1 '11 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." –  Jeff V Oct 1 '11 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. –  S.Lott Oct 2 '11 at 0:25

2 Answers 2

up vote 1 down vote accepted

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@home.com      561
2  otheremail@home.com 561
3  meagain@home.com    561
4  same@home.com       561

If the user logs in with "otheremail@home.com" 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)

share|improve this answer
    
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! –  Jeff V Oct 1 '11 at 5:41

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.

share|improve this answer
    
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. –  Jeff V Oct 1 '11 at 5:31

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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