Optimize Your Database Tables for Facebook Connect
August 5, 2008
At this year’s f8 Conference, Facebook announced the release of Facebook Connect. Facebook Connect is essentially Facebook Beacon redone, with the additional goal of taking users’ privacy into account. (The name change is likely to prevent people from associating negative opinions of Beacon with Connect.) Third party websites can allow users to log in using their Facebook credentials instead of creating a username and password for yet another website. In turn, those websites can deliver information to users’ News Feeds and Mini Feeds, generate friend notifications, and send email messages via Facebook.
The Run Around
Since seeing the first demo video of Facebook Connect during Mark Zuckerberg’s f8 ‘08 Keynote, one of the my initial questions that came to mind is how the back-end database must be structured for this to work efficiently and effectively. Fortunately, Facebook came along and offered The Run Around sample web app, which demonstrates how to use the client and includes example code. With the sample code came the users table definition, which can be seen below.
CREATE TABLE `users` ( `username` varchar(255) NOT NULL, `name` text, `password` text, `email` text, `fb_uid` int(11) default NULL, `email_hash` varchar(64) default NULL, PRIMARY KEY `username` (`username`) );
From this, it’s clear that the username is the primary key for any given user, as would generally be expected. However, considering that the username is in fact the primary key, and keeping in mind that users are allowed to log in using Facebook data alone via Facebook Connect (thereby not needing a username), I became curious about what kind of username a Facebook user is assigned when attempting to log in using this method.
After a bit of searching, I came across the createFromFacebookUID function within the lib/user.php file. This function (less some unnecessary lines of code) can be seen below.
static function createFromFacebookUID($fb_uid) {
// *extraneous code stripped out for simplicity*
$user_params['username'] = 'FacebookUser_' . $fb_uid;
// *more extraneous code stripped out for simplicity*
// write to the db
if (!$user->save()) {
return null;
}
return $user;
}
From this, it can be seen that the username field is nothing more than a hack for Facebook users. In fact, someone not using Facebook could come along and create a user by the name of “FacebookUser_12345″, at which point the person registered under Facebook UID 12345 could hijack that person’s account simply by logging in via Facebook Connect. While this is not a likely scenario, it is still a scenario that does exist in the system and is thus a design flaw.
While thinking about ways to resolve this issue, I considered the following approaches.
Potential Resolution #1: Create a Separate Table for Facebook Users
My initial thought was that this could be corrected simply by having two tables: a users table (containing people who registered through the site) and a facebook_users table (containing people logging in via Facebook Connect).
CREATE TABLE `users` ( `username` varchar(255) NOT NULL, `password` text, `name` text, `email` text, PRIMARY KEY `username` (`username`) ); CREATE TABLE `facebook_users` ( `fb_uid` bigint(11) default NULL, `email_hash` varchar(64) default NULL, `name` text, `email` text, PRIMARY KEY `fb_uid` (`fb_uid`) );
At first this seemed like a viable approach until I considered that other data in the database is going to be associated with a user. For example, a website that allows users to review movies might store each user’s reviews in a relational table named something like user_reviews, which includes two foreign keys pointing to the users and movies tables respectively.
Now that we’ve introduced the facebook_users table, however, we quickly see that reviews data cannot be associated with persons using Facebook Connect unless we either create a secondary facebook_user_reviews table — which is not feasible as all interfaces interacting with reviews data must be updated — or somehow associate the facebook_users table with users.
Potential Resolution #2: Associate Facebook Users Table with Users Table
The previous statement brings me to my second though on how this conundrum might be approached. Simply associating facebook_users with users allows for us to use JOIN conditions across multiple tables, thereby allowing other user data associated with the application (such as the aforementioned movie reviews) to be retrieved and associated with a Facebook user. The table structure implementing this concept can be seen below.
CREATE TABLE `users` ( `username` varchar(255) NOT NULL, `password` text, `name` text, `email` text, PRIMARY KEY `username` (`username`) ); CREATE TABLE `facebook_users` ( `fb_uid` bigint(11) default NULL, `email_hash` varchar(64) default NULL, `user_id` int(11) NOT NULL, PRIMARY KEY `fb_uid` (`fb_uid`) );
Unfortunately, this method requires a Facebook user logging in via Facebook Connect to register an account on the website as the two tables are directly related. As such, a user is unable to take advantage of what Facebook Connect is intended for: one-click login. The user would need to both log into Facebook Connect and register/login with an account on the third party website.
Potential Resolution #3: Create a Separate Account Table that Users and Facebook Users Associate With
My third and final thought on how to approach this difficulty is to create a basic account table with two separate tables associated with it: one with login credentials for when visitors create an account, and another with Facebook UID’s that can be used for Facebook Connect. The below SQL table definitions specifically outline my intentions.
CREATE TABLE `accounts` ( `id` int(11) AUTO_INCREMENT NOT NULL, `name` text, `email` text, PRIMARY KEY `id` (`id`) ); CREATE TABLE `users` ( `username` varchar(255) NOT NULL, `password` text, `account_id` int(11) NOT NULL, PRIMARY KEY `username` (`username`) ); CREATE TABLE `facebook_users` ( `fb_uid` bigint(11) default NULL, `email_hash` varchar(64) default NULL, `account_id` int(11) NOT NULL, PRIMARY KEY `fb_uid` (`fb_uid`) );
Using this structure, whenever a person logs in using Facebook Connect, two records will be inserted into the database: one record in the facebook_users table, and one in the accounts table. Similarly, whenever a person registers a new account on the website, two records will be inserted into the database: one record in the users table, and one in the accounts table.
The benefit of this approach is that all other data in the database can still be associated with an account. The account is then either associated with a Facebook user or a registered site user depending on how the person logged into the site during the visit.
Additionally, because the accounts table is utilized regardless of how the user logs in, and because data specific to the login method is kept in a separate table for each login type, more types of login methods — OpenID for example — can easily be implemented into your website in the future.
In Conclusion…
It is my personal belief that the final suggestion provided in this article is the best route to take for most websites wishing to implement Facebook Connect. Depending upon the goals of your website or business, though, you might not need to go the extra mile. It is important to note that if you do decide implement one of the “easier” implementations, such as the one included with The Run Around, then you will be making things extremely difficult for yourself in the future should you change your mind. While I suggest that something similar to the final table definitions be used within your own web projects that are implementing Facebook Connect, it is not the only right or only working solution.
Comments
32 Responses to “Optimize Your Database Tables for Facebook Connect”
Got something to say?
It is possible on PC, but not possible on mobile.
How can i use facebook connect on mobile?
[…] Optimize Your Database Tables for Facebook Connect : Facebook Developer "…one of the my initial questions that came to mind is how the back-end database must be structured for this to work efficiently and effectively. Fortunately, Facebook came along and offered The Run Around sample web app,…" (tags: facebook db sql optimization webdev) […]
Man I like your step by step tutorials, they are very clear and easy to understand, keep the good work.
I am waiting for the new facebook application migration tutorial
Matt,
I am not sure why there is a problem with the initial solution?
Here is how would implement it using the original users’ table.
Don’t store email_hash for your users.
When someone tries to connect their FB a/c with your a/c. Simply create the hash and pass it to FB.
If the hash matches, FB will return the hash in the user’s data. You simply update the record with the hash.
All the users with email_hash non null are your FB users and rest all are your own users.
Alternate solution:
In your original table add a field ‘is_fb_user’. When adding users from your website, always mark it as 0 and when adding an FB user mark it as 1.
Matt,
In Resolution 3 you have email column in accounts table. How do you populate this column if the user is a Facebook User?
In other words, how can you get an email address for a facebook user?
Thanks
Arun
@Gaurav - the problem with the initial method is explained in this paragraph from above:
@Arun - I see what you’re saying — because email is not information available from Facebook, it should have been included with the `users` table instead of the `accounts` table. What I was trying to demonstrate, however, is that an account can have communal information provided by a website user once the user is logged in — regardless of the login method (normal for FB connect). So if you have a “preferences” or “my account” page on your website, the user could provide his or her email address in a provided text input element, at which point it would update in the `accounts` table.
You could also just prevent other users from using “FacebookUser_%” in their username. You can validate form input, after all.
You could, but I wouldn’t call that “optimized” since it’s still just a hack.
I must admit
This is the best facebook tutorials website.
Thanx
whoa 3 tables… :))
this is so funny…
“FacebookUser_12345″, at which point the person registered under Facebook UID 12345 could hijack that person’s account simply by logging in via Facebook Connect.
LOL!!!
Great Article! Thanks!
I will use Solution 3 for my new site. In my opinion it has the clearest structure and if i want to add other id providers it´s very easy to extend.
Of course, for registrations on your own site you could deny usernames like “facebook_%”, but as you explained there are more drawbacks than this.
Hi Matt,
Thanks for the good article, has helped me along my way with integrating facebook connect for my site. I have a question for you; Regarding users that initially sign up through my site the traditional way by entering their details, who then come back at a later date and login using facebook connect. Do you have any thoughts on how to deal with this..? Seeing as my site will remember user favourites and other interactions, having two separate logins makes doing this a little harder. Apart from prompting after a user uses facebook connect asking whether they are a member already, I haven’t really got an answer. Thanks in advance for any advice.
Steven
Hey great info about facebook applications.
Hi Matt,
you saved my day, thank you!
@Steven: If you store data in the ‘accounts’ table you shouldn’t have problems since all the interaction with your website will be associated with 1 single account. The 2 additional tables (or 3 if you add OpenID) will serve only for login purposes, at least that’s what I understand from the post.
@Salva: Are you suggesting then that if a user who has signed up through the website tries to login via facebook connect, the function will see that the email is already in the database? I’m also trying to figure this out.
Thanks!
@Keith
From the article: “Using this structure, whenever a person logs in using Facebook Connect, two records will be inserted into the database: one record in the facebook_users table, and one in the accounts table. Similarly, whenever a person registers a new account on the website, two records will be inserted into the database: one record in the users table, and one in the accounts table.”
It seems possible then to create a method that handles the situation in which a user’s email is found in the ‘accounts’ table but not in the ‘facebook_users’.
Hey great info about facebook connect. Facebook connect is really incredibly great and we have to set its database carefully. Thanks
If you are always populating all three tables for each user then what is the purpose of having three tables?
Why not use one table with the primary key of an auto incremented value, as in the accounts table in solution 3, and add columns to the table as needed. Want to add open id? Fine add the necessary columns to the user table and populate them with null values for current users.
Gooood Thnk You
great, this can help me develop facebook status update from my forum. thanks.
very good approach
this is your text
op[
Thank you for writing
Thanx
[…] login information (fb_id, …) from the account information (e.g. firstname, surname, …): http://facebook-developer.net/2008/08/05/optimize-your-database-tables-for-facebook-connect/The proposed solution of this article are three tablesCREATE TABLE accounts ( id int(11) […]
Hi Matt(and all who see this!),
Great Tutorial, But I have a doubt :
Creating Tables is Fine , i.e Users,facebook_users,account and movies like :
CREATE TABLE `accounts` (
`id` int(11) AUTO_INCREMENT NOT NULL,
`name` text,
`email` text,
PRIMARY KEY `id` (`id`)
);
CREATE TABLE `users` (
`username` varchar(255) NOT NULL,
`password` text,
`account_id` int(11) NOT NULL,
PRIMARY KEY `username` (`username`)
);
CREATE TABLE `facebook_users` (
`fb_uid` bigint(11) default NULL,
`email_hash` varchar(64) default NULL,
`account_id` int(11) NOT NULL,
PRIMARY KEY `fb_uid` (`fb_uid`)
);
CREATE TABLE `movies` (
`movie_id` bigint(11) default NULL,
`movie_name` varchar(164) default NULL,
`comment` blob ,
PRIMARY KEY `movie_id` (`movie_id`)
);
But When Come ‘user_reviews’ Table We can Use Some like Schema
CREATE TABLE `user_reviews` (
`review_id` bigint(11) default NULL,
`review` varchar(640) default NULL,
`mov_id` bigint(11) NOT NULL,
`account_id` int(11) NOT NULL,
PRIMARY KEY `review_id` (`review_id`)
);
is my assumption is valid ? Please Advise me .waiting your reply
Thankfully
Anes P.A
Good idea. but is true.
[…] code. With the sample code came the userstable definition, which can be seen below. view plaincopy to […]
Pretty section of content. I just stumbled upon your site and in accession capital to assert that I get actually enjoyed account your blog posts. Anyway I’ll be subscribing to your feeds and even I achievement you access consistently quickly.
good article thank…
CREE XR-E LED Rechargeable Torch Flashlight Black K35 is 1 such LED flashlight, which is made from high strength t6061 aeronautic aluminium alloy by CNC technologies. It is powered by 118650 high power memory free eco friendly Li-ion rechargeable battery. Its capacity is 2400 mAh high capacity. The maximum lighting distance covered from this LED torch is 200 metres. This is a brand new torch and we will discuss some of its internal feature that could enable you in deciding whether or not to obtain this product or not. It is a black coloured torch with 180lm brightness. It makes use of rechargeable battery of 1*18650. As the shell material is having great strength, it can effortlessly put on resistance although it goes under Anodic oxidation therapy. The lifetime of this Cree XR-E LED Rechargeable Torch Flashlight Black K35 is up to 100,000hrs. It covers 200 metres distance with its light. The voltage, at which it operates, is three.7V with operating current to be of 750mA. It can have runtime storage for 2 hours, which additional depends on the battery and how significantly it had been charged. You have distinct buttons that offers you diverse modes of its operations.
Good day! I know this is kinda off topic but I’d figured I’d ask. Would you be interested in exchanging links or maybe guest authoring a blog post or vice-versa? My site covers a lot of the same subjects as yours and I think we could greatly benefit from each other. If you are interested feel free to send me an email. I look forward to hearing from you! Terrific blog by the way!