Designing Table Joins

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!

No comments:

Post a Comment

Comments on this blog are moderated and we do not accept comments that have links to other websites.

Share

About Isaac Sacolick

Isaac Sacolick is President of StarCIO, a technology leadership company that guides organizations on building digital transformation core competencies. He is the author of Digital Trailblazer and the Amazon bestseller Driving Digital and speaks about agile planning, devops, data science, product management, and other digital transformation best practices. Sacolick is a recognized top social CIO, a digital transformation influencer, and has over 900 articles published at InfoWorld, CIO.com, his blog Social, Agile, and Transformation, and other sites. You can find him sharing new insights @NYIke on Twitter, his Driving Digital Standup YouTube channel, or during the Coffee with Digital Trailblazers.