Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm building a site ("The Site") and I'm considering how to design the DB schema for it.

I have a few scenarios that will affect the outcome:

  • The user can choose to register a native account on the site, which will make him use that account for login in to his account on the site in the future.
  • The user can choose to login via another site ("external site") (such as facebook connect, google, foursquare, etc..), which will make him use that for login in to his account on the site in the future.
  • Doesn't really matter which login method he chose (native account or external site account), the user will be able to authenticate his account on the site with external sites (such as facebook, twitter, instagram, etc...) for using the data he has on the external site (such as his tweets from twitter, his photos from flickr, etc..), in the site.

How should I design the schema of the DB ?

For the first two scenarios, I thought of having a table called Users that has an id, and a type fields. The type reference a different table, such as FacebookUsers or GoogleUsers, each has a user_id field - which has a foreign relation to the Users.id field - and other fields such as the first_name, profile_picture, etc..

However:

  1. I'm not sure how smart it is to use this approach.
  2. I don't know how to implement the third scenario in such an approach.
share|improve this question
    
I'm closing this as off-topic, because this is a website architecture/design/business logic question than a database design question. –  Coder Hawk May 13 '11 at 19:07
add comment

closed as off topic by Coder Hawk May 13 '11 at 19:08

Questions on Database Administrators Stack Exchange are expected to relate to database administration within the scope defined by the community. Consider editing the question or leaving comments for improvement if you believe the question can be reworded to fit within the scope. Read more about reopening questions here.If this question can be reworded to fit the rules in the help center, please edit the question.

1 Answer

There is a similar question in stackoverflow

And the solution is

You need 2 additional fields in your table, one is the login source (twitter, openID, facebook) and the other is the source user id (the id provided from twitter, openID, facebook) I recommend making this a string as some log in sources have letters in their user IDs and not just numbers. more

  • I'm not sure how smart it is to use this approach.

This approach is good, actually sites opting it. see SO login page http://stackoverflow.com/users/login

  • I don't know how to implement the third scenario in such an approach.

I think you can implement this, only if the thrid party sites provide access. This question is off-topic here, you can ask this in stackoverflow

share|improve this answer
add comment

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