Table joins are on my mind today.The site I’m working on, TripConnect, allows users to click on places that they’ve visited. When you register you’re presented with a page listing out aprox 1000 locations that you can click on and say I’ve been there. The site is a travel advice network, so when you click on a location, you’re essentially telling people in your network that you’ve been to the location and may be able to provice advice. Other pages on the site let you rate these places and add travel reviews.

Here’s a link to my travel profile so you can see what I mean.

Anyway, it turns out that users love this feature and click on lots and lots of places. But this poses somewhat of a technical challenge. We have users in one table and the locations they clicked on in a related table. If we have N users and each user clicks on M locations, the table that stores the locations for each user will have N*M rows. Trust me, this is getting to be a big number!

This is just one example of the many:many joins that I deal with and I’m still wrestling with good ways to design the structures and associated queries for this type of data. Feel free to comment here if you have some ideas!