SQL Design Pattern: how do I store multiple unique ids from different sites in mashup?

Submitted by Derick Ng on 1 February 2010 - 11:41am

I'm building a mash-up to store meta-data on items from multiple REST API datasources. I'd like to be able to generate typical feeds (most recent, top rated, most viewed, etc) based on data summarized across all the different datasources, and also add tags (i.e. many-to-many relationships).

My problem is that each datasource has a different way to issue unique ids through their REST API. I need suggestions on the best pattern to use for my MySQL datamodel.

My current solution is to use 1 table for all items and a composite key but the joins are long and cakePHP doesn't deal with composite keys natively: 

datasource_id smallint,  
datasource_item_id VARCHAR(36), // some datasources issue alpha keys

Q: Is it ok/better to add an auto increment primary key to my table and translate all my internal joins/indexes from external UIDs to my internal UIDs? :

id int(10) unsigned NOT NULL auto_increment,

Q: Are enums an efficient datatype for storing datasource_id (should have maybe 10 different datasources)?

Q: Are there other approaches that yield better, more scalable results in the long run?