what is the design of database to use openID?

Question

I want to use janrain for my login system, Everything is ready, but i dont know how to build my database?what is the database design to use openID? specially when some users have some different providers how can i know who they are?are they new user or the old one?i want a complete database design for it

Answer 1

add a column for the openId in the table where you are storing the user information.

An example would be as below :

create table users (
first_name varchar(50),
last_name varchar(50),
email_address varchar(100),
openId varchar(150));

you can also set an index for the openId so that searches are faster when you are logging the user into your application. Also add the UNIQUE constraint so that two rows are not present for a user with the same openId

Answer 2

All you need is a column where you store the identity URL returned by whatever OpenID library you are using.

You might consider putting that in a separate table which has only identity and userid columns so a user can have more than one openid assigned to his account.

Answer 3

I would suggest you something that I did in my application. I had a column in the users table that is boolean. 1 if the user uses openID and 0 if not. Another table would be the opneID table which has the provider and the key provided to them when they use open ID. So you will have three scenarios as follows:

1. User is already logged in and wants to add openID for next time he logs in.
2. User is already registered but wants to use openID for next time he logs in.
3. User is not registered and wants to log in using OpenID.

This is what I did:

1. The details are already stored so lookup for current logged in user and make the openID boolean to true and add the openID details he will provide.

2. When he clicks on openID ask him if he is already registered and if he is ask for email ID and password and then use openID details.

3. (This is the hardest part) Make the openID boolean 1 and add the details of OpenID and then give him a form which just asks for important details whatever is suitable for your application. Depending on the scale of your application you might not need the second form. The application I was working on needed extra information from user. If you want to skip the second step you might look into some of the options given by Facebook API to retrieve the first_name last_name etc details and save.

So when user is logging in check if the OpenID boolean is set to 0 or 1 if it is set to 1 then use the corresponding OpenID details that you might have saved with proper joins.

The hardest part I found was the third step where in I should somehow not take any password from the users who are using OpenID. This was violating the db constraints. I couldn't take off the constraint because that would cause problems for other users who want to register. So I had to somehow circumvent the constraint for the openID users. It might not be the best solution but it might help you. Good Luck!!