SQL design pattern: how can I store several unique identifiers from different sites in mashup?

Question

I am creating a mash-up to store metadata on elements from multiple REST API data sources. I would like to be able to generate typical channels (the latest, highest rated, most viewed, etc.) based on data summarized in all different data sources, and also add tags (ie many-to-many relationships) .

My problem is that each data source has a different way of issuing unique identifiers through its REST API. I need suggestions on a better template for my MySQL datamodel.

My current solution is to use 1 table for all elements and a compound key, but the joins are long and cakePHP does not handle compound keys initially:

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

Q: Is it possible to add / add a primary key of automatic increase to my table and transfer all my internal joins / indices from external UIDs to my internal UIDs ?:

id int (10) unsigned NOT NULL auto_increment,

Q: Are enumerations an effective data type for storing datasource_id (should they possibly have 10 different data sources)?

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

Answer 1

Basically, I can only confirm the solutions that you have already considered.

, , , ( SQLite 2 SQLite 3 ), .

, , , ( , , ), , , : id . , , - , . cakePHP , .

. , , , , . .

SQL , . SQL , .

. ; , , ( MySQL CHECK).

Q: datasource_id ( , , 10 )?

ENUM 1 2 , . . 4 /. , .