Browse > Home / Tutorials / Optimize Your Database Tables for Facebook Connect

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.

Advertisement

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.

Related Articles

Share on Facebook      Share This

Written by Matt Huggins · Filed Under Tutorials 

Comments

4 Responses to “Optimize Your Database Tables for Facebook Connect”

  1. Kim on August 6th, 2008 3:24 am

    It is possible on PC, but not possible on mobile.
    How can i use facebook connect on mobile?

  2. links for 2008-08-06 [delicious.com] at Ip’s. on August 6th, 2008 2:41 pm

    […] 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) […]

  3. Felipe on September 17th, 2008 2:00 am

    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

  4. Gaurav on October 1st, 2008 1:52 am

    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.

Got something to say?





Close
E-mail It