| Author |
Topic |
|
Vinnie881
Master Smack Fu Yak Hacker
1231 Posts |
 Posted - 2011-03-06 : 12:26:58
|
I have a predicament where I have a database that runs a website.This website has about 50 tables currently, but is constantly expanding (i.e. new tables that allow new functionality).There are 3 ways a user can login to the website1. Username and password (Which most users do not use)2. Facebook login button3. Twitter login button.When a user logs in I identify the account and reference the database for our site userid which is used in about 90% of the tables as a index we use for a lookup.Here's the prediciment. 1. User logs in via facebook, We create an account automatically2. The user once logged in via facebook logges out and the next time he comes login via twitter which creates a seperate account.It is not until they login via both accounts during the same session where I know that those accounts belong together, So once I identify that two accounts belong together (I won't go through the logic, but it works), I need to merge the two accounts so they work as 1.I can only think of 3 approaches, none I like.1. I can use foreign key contraint with cascading update to simply change the table values for the old user to the new, but as this is a website it needs to be easily ported to mysql which is not friendly to cascading updates unless I limit my tables to innodb(not important if you don't know what that is, just assume it limits my options).2. At the time of merge go through all tables that are affected and update them with a simple update statement to the new id. Issue with this is it's a ongoing process since the db is constantly growing and can cause many future issues.3. I can create a subtable that stores both accounts in it and rewrite all procecedures to use a function that returns the id I want and link on it (This is the ideal approach as this way I am not updating table records, but it requires the most work.)Any thoughts, idea's or other approaches?Thanks
Success is 10% Intelligence, 70% Determination, and 22% Stupidity.\_/ _/ _/\_/ _/\_/ _/ _/- 881 |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2011-03-07 : 13:02:25
|
| Does it matter for the website that all the different logins for a single "user" are tied together? Or does it only matter for some sort of business intelligence activities? Just curious. |
 |
|
|
Vinnie881
Master Smack Fu Yak Hacker
1231 Posts |
Posted - 2011-03-09 : 22:36:16
|
It is for the sole purpose of user experience (it is basically to accommodate a very small percentage of users who by using certain steps can skip our identification process. There is no benefit to the user to have two separate accounts, and it creates more work for them.) It really is not used for business intelligence or anything else though, just user experience.
Success is 10% Intelligence, 70% Determination, and 22% Stupidity.\_/ _/ _/\_/ _/\_/ _/ _/- 881 |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2011-03-10 : 08:21:23
|
| This merging process...what is its purpose? And how are you able to map the custom/twitter/facebook accounts without the users interaction? As far as I can see the only way to do this is to create a page where you let the user merge his/her account(s) together and choosing which of them they actually want to use.- LumbagoMy blog-> http://thefirstsql.com/2011/02/07/regular-expressions-advanced-string-matching-and-new-split-function-sql-server-2008-r2/ |
|
|
|
Vinnie881
Master Smack Fu Yak Hacker
1231 Posts |
Posted - 2011-03-10 : 13:45:58
|
The question is not really how do I know how to merge an account. That is already done and correctly functioning. The purpose of the merging process is a little complex, but the issue is not logic, the issue is the best approach on how to do this in the database (i.e.merging/identifying what data belongs together in the database and how to associate for querying as I need the accounts to function as one). I can go through the entire logic, but that would require a detail explanation of what is being accomplished and the reasons, which I am not sure if it is beneficial. The ONLY issue I am faced with is the best method for combining the accounts. Thanks for your help, but the issue is not identifying or knowing what information goes where, it's once I know that two accounts need to be merged, what is the best way to accomplish combining.Thanks again.
Success is 10% Intelligence, 70% Determination, and 22% Stupidity.\_/ _/ _/\_/ _/\_/ _/ _/- 881 |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2011-03-11 : 03:07:21
|
| I'm a little confused about the requirements, and what I really don't understand is how the two/three accounts can appear as one. I don't know what kind of site you are running, but lets say the user can send messages to each other. Now, if I have two accounts mapped together...how will you decide which account will be the sender of my message? If this isn't relevant to your situation, as far as I can see there are basically only two options: create a junction table with a unique ID and use that unique ID throughout your system (your option #3). The other alternative is to create some new columns in your existing custom login table (option #1) with the facebook/twitter information you need, and use that UserID or whatever (not the facebook/twitter account name) as the unique identifier throughout your system.- LumbagoMy blog-> http://thefirstsql.com/2011/02/07/regular-expressions-advanced-string-matching-and-new-split-function-sql-server-2008-r2/ |
|
|
|
lappin
Posting Yak Master
182 Posts |
Posted - 2011-03-11 : 06:43:16
|
| This may be an over-simplification - but can you not ask user to supply an email address as part of the account creation process and use this to identify if the user already has an account? |
|
|
|
Vinnie881
Master Smack Fu Yak Hacker
1231 Posts |
Posted - 2011-03-16 : 01:40:48
|
Issue is resolved and did it the old fasion way just grunt coding. Lappin, as there is no account creation process that requires user input (That's the reason behind the connect api's, and also it would drastically impact user experience since we work as an app on facebook as well, which it would break flow if we requried additional account creation steps.Lumbago, the accounts work as one, they both point to the same user, just different logins that point to that userid.
Success is 10% Intelligence, 70% Determination, and 22% Stupidity.\_/ _/ _/\_/ _/\_/ _/ _/- 881 |
|
|
|
|
Need a wise programmer's opinion.