How to unify overlapping id systems into a single system?

Question

What's the best way to unify several overlapping id systems into a unified one while maintaining the old id system.

I have several different ids on my website... (E.g /publisher/1234 and /designer/1234) I would like to unify the ids into a new system, but want to preserve the functionality of the older system.

Answer 1

bridge table

column newId
column oldId

Answer 2

@Steven A. Lowe 's answer is certainly the simplest, least impact on existing applications.

Just for grins and giggles, let's assume that you may want to make other changes to the schema as well (new tables, columns).

I'd look carefully at the tables before combining anything - make sure the entities/relationships represented by the tables are cleat and normalized. If publisher and designer really are the same thing, the same entity, then a new table combining the two might be desirable.

So create the new table, with a new unique id. There are several ways to then associate this id back to the original tables. Structuring the 'history' (where it came from and the old id) are going to require a couple of columns, ideally in a separate table (because they really aren't 'about' the new table, right?), but they could be included in the new table definition.

Something like

new table
   column newId
   .
   .
   .
   column oldTableName
   column oldId

or

new table
   column newId
   .
   .
   .

join table
   column newId
   column oldTableName
   column oldId

You could then create one or more views that presents the old table structure to the existing applications.

create view oldTable id, [...] as
   select oldId, newTable.col1, newTable.col2
    where oldTableName = 'oldTable

There are other methods to merge ids, but the two described here are probably sufficient.