Database design: merge users from different logging systems (google, facebook, openid ...)

Question

Hallo, I need to merge users from several souces some how, for example facebook, Google, plaxo... Currently I have this structure in my database:

USERS_MYSITE

mysite_user_id        | parameter | value
------------------------------------------
223                   | firstname | Tom
223                   | lastname  | N.
223                   | birthdate | 1985-01-30

USERS_FACEBOOK

mysite_user_id        | facebook_user_id | parameter | value
-------------------------------------------------------------
223                   | 456353453        | fname     | Tom
223                   | 456353453        | lname     | N.
223                   | 456353453        | birth     | 1985-01-30

USERS_GOOGLE

mysite_user_id        | google_user_id | parameter | value
-----------------------------------------------------------
223                   | tomtom22       | fn        | Tom
223                   | tomtom22       | ln        | N.
223                   | tomtom22       | brt       | 1985 JUN 30

USERS_VIEW

    mysite_user_id        | remote_user_id | site_name | parameter | value
    ---------------------------------------------------------------------------
    223                   | 223            | mysite    | firstname | Tom
    223                   | 223            | mysite    | lastname  | N.
    223                   | 223            | mysite    | birthdate | 1985-01-30
    223                   | tomtom22       | google    | fn        | Tom
    223                   | tomtom22       | google    | ln        | N.
    223                   | tomtom22       | google    | brt       | 1985-01-30
    223                   | 456353453      | facebook  | fname     | Tom
    223                   | 456353453      | facebook  | lname     | N.
    223                   | 456353453      | facebook  | birth     | 1985 JUN 30

Then SELECT FROM USERS_VIEW WHERE mysite_user_id = '223' and i got all user information. After that i can use several transporation arrays, to transform all remote data to my format

Array("firstname" => Array("fn", "fname"), "birthdate" => Array("brt", "birth"), ...)

same goes with values. Next depending on what user selected as his primary data i can show it.

Problem is that I've never done it before, so maybe somebody knows how to do it better. Please share your ideas.

Thank you.

Answer 1

You've probably already solved your problem by now - but in case you still need help, I'm happy to help. This is the kind of problem I like.

But in order to help, can you tell me what final outcome you actually want? You've got a solution that should work and I'm not sure if you're saying that you want it to produce a different outcome or that you want it to produce the current outcome but more efficiently?

If you can clarify that, we can sort this out.

Answer 2

Hi, the idea was to create a engine, which could combine many accounts from many different "account holders", with possibility to add new if needed. Plus, give possibility to user customize his account data; to take first name from one source, last name from another and add from profile form. I worried about query speed, cause it's quite risky to make such slow query every time for every user shown on screen. Also we get a big traffic, about 1 million a day, that's 20 million page views, and about 100 000 000 query executions. That's a big count.

Yes, the problem is already solved. I just created another table, with duplicated data :( . Every time user changes some of his settings a new table take update from structure above. Then we taking data only from that new table, and that method works fine. Already added linked in and twitter to sources list. Currently thinking to export that engine and make it open source. :)