Facebook Cookbook - Jay Goldman [115]
owner (user ID for whom you want to find friend lists; can only be the loggedinuser)
See Friend List Table for more information.
friendlist member
Relationship table showing which friends of a specific user are in a specific friend list. You can query this table only when the friend list is owned by the loggedinuser, and you can display this information only to the loggedinuser, as it’s considered private. You can query on:
flid (friend list ID of a friend list belonging to the loggedinuser)
See Friend List Members Table for more information.
group
Storage for the groups created in the Facebook Groups app. You can query on:
gid (group ID of the group you’re looking for)
See Groups Table for more information.
group member
Relationship table showing which users are in which groups. You can query on:
uid (user ID of a user whose groups you’re looking for)
gid (group ID of a group whose members you’re looking for)
See Get Group Members for more information.
listing
Storage for the listings created in the Facebook Marketplace app. You can query on:
listing_id (listing ID of the listing you’re looking for)
poster (user ID of the user whose listings you’re looking for)
See Listing Table for more information.
page
Storage for the pages created in the Facebook Pages app. You can query on:
page_id (page ID of the page you’re looking for)
name (name of the page you’re looking for)
See Page Table for more information.
page_fan
Relationship table showing which users are fans of which pages. You can query on:
uid (user ID of the user whose pages you’re looking for)
See Page Fan Table for more information.
photo
Storage for photos added to the Facebook Photos app. You can query on:
pid (photo ID of the photo you’re looking for)
aid (album ID of the album whose photos you’re looking for)
See Photo Table for more information.
photo_tag
Relationship table showing which photos have been tagged with which users. You can query on:
pid (photo ID of the photo you’re looking for)
subject (user ID of the user who you’re looking for in the photos)
See Photo Tag Table for more information.
user
Storage for Facebook users. You can query on:
uid (user ID of the user you’re looking for)
name (full name of the user you’re looking for)
See User Table for more information.
Advanced Relational Database Table Optimization
—Pete Forde with Rowan Hick (see their bios in Contributors)
Problem
I have the problem everyone wants: popularity! Even with a proper caching strategy and sensible indexes in place, my application database is quickly becoming a bottleneck. Like most web applications, my resources tend to be frequently read, with relatively infrequent updates. I have adhered to common design best practices; my schema is properly normalized. What more can I do to scale my database throughput capacity?
Solution
The solutions listed next won’t work for everyone, but there’s a pretty good chance that they’ll solve some of your problems.
Denormalization
Are you consistently joining the same tables together? You might consider adding redundant columns to your high-traffic tables so that you can reduce or eliminate joins. For example, given a Users.province_id fk> Province.id relationship, it might make sense to create a Users.province column and store the literal text value directly on the Users table.
Of course, they call these databases “relational” for a reason! By giving that up, you accept the burden of updating the same data in multiple places. You might have to change how your application retrieves these new redundant values. Profile to find bottlenecks, and weigh the tradeoff cost of each optimization.
In most cases, it’s not recommended that you drop or abandon your normalized join tables completely; you should add redundant columns only for data that you require