Adam Green
Twitter API Consultant
adam@140dev.com
781-879-2960
@140dev

Engagement Programming: User ids are the correct key, not screen names

by Adam Green on November 20, 2013

in Engagement Programming

When you start programming with the Twitter API, the natural tendency is to identify users in your database and code by their screen name. It seems right, since that is how you identify them when using Twitter. I used to do that too until I found out that users in some communities change their screen names a lot more than expected. Twitter allows you to change an account’s screen name and still retain all tweets, friends, followers, etc. associated with the account. So if you’ve built a relational database of tweet data based on screen names and one of them changes, all your pointers for that user break. The only safe way to identify users is through their account’s user id. This is assigned when an account is created, and it never changes.

You can see a real-world database schema that follows this model of user ids as the linking value in the source code for my engagement programming book. The only place the screen name is recorded is in the users table. This field is updated with the current screen name every time my code refreshes the user records.

The only annoying aspect of programming with user ids, is that you will need to do a lookup in your database to convert the user id into a screen name so it can be displayed on web pages or in an app. For example, when I need to deliver tweets for display, I link the tweet to its user as follows:

SELECT tweets.*, users.screen_name
FROM tweets, users
WHERE tweets.user_id = users.user_id

Leave a Comment

Previous post:

Next post: