Normalizing a common ID type shared across tables

This is a simplified version of the problem.

We are required by external customers to have several "public" ids
they can query our data by. For simplicity, we'll call them "pid" and
"crid". We have an "entity" table which stores this information.
Something like this:

entity
--
entity_id
pid
crid
musicbrainz_id

Then we have separate entities such as "episode", "series" and
"broadcast". Each of these has an entity_id pointing to the entity
table.

How can external customers search, via pid or crid and get the
appropriate episode or series, along with proper identification of
what it is? Given a pid, we can fetch the entity id, but then we need
to search the episode, series and broadcast tables for this value
(actually, we need to search a bunch more). Further, not all ids will
necessarily be related to all of the other tables.

Strategies:

1. Find the entity id for a pid and search every other table for the
pid.
2. Put an "entity_type" column on entity, but what if it's a pid in
the episode table but we accidentally set episode.type as series? We
don't want to duplicate data and I don't want to put database metadata
into column values.

Option number 1 is slow and seems wrong (further, the various tables
have different structures making problematic).

Option 2 means duplicate data and this data can get out of synch. We
can use triggers for force this, but this seems really nasty and, in
any event, bugs in the implementation of mysql triggers have hit us
several times.

What's option 3?

Cheers,
Ovid

On Oct 21, 5:59 am, Ovid wrote:
> This is a simplified version of the problem.
>
> We are required by external customers to have several "public" ids
> they can query our data by.  For simplicity, we'll call them "pid" and
> "crid".  We have an "entity" table which stores this information.
> Something like this:
>
>   entity
>   --
>   entity_id
>   pid
>   crid
>   musicbrainz_id
>
> Then we have separate entities such as "episode", "series" and
> "broadcast".  Each of these has an entity_id pointing to the entity
> table.
>
> How can external customers search, via pid or crid and get the
> appropriate episode or series, along with proper identification of
> what it is?  Given a pid, we can fetch the entity id, but then we need
> to search the episode, series and broadcast tables for this value
> (actually, we need to search a bunch more).  Further, not all ids will
> necessarily be related to all of the other tables.
>
> Strategies:
>
> 1.  Find the entity id for a pid and search every other table for the
> pid.
> 2.  Put an "entity_type" column on entity, but what if it's a pid in
> the episode table but we accidentally set episode.type as series?  We
> don't want to duplicate data and I don't want to put database metadata
> into column values.
>
> Option number 1 is slow and seems wrong (further, the various tables
> have different structures making problematic).

why is it slow? Are you literally selecting from ALL the other tables
at once?

>
> Option 2 means duplicate data and this data can get out of synch.  We
> can use triggers for force this, but this seems really nasty and, in
> any event, bugs in the implementation of mysql triggers have hit us
> several times.

Where the hell does entity type come in??? How does that help?
What are you really trying to do?

>
> What's option 3?

materialized views? Oh wait mysql doesn't have those does it?

>
> Cheers,
> Ovid


If you created a view of the other tables (and this is a WAG since you
give so little info on the data model and what you really are trying
to do), for example:

create view cv_episode --- customer view of episode table
as
select entity.pid, episode.(columns other than entity_id)
from episode , entity
where episode.entity_id = entity.entity_id; --- assumes entities that
are episodes have pid values that match.
--- in fact this makes the BIG assumption that episode has a pid
column, since you did not say
--- if it had pid or crid (whatever the hell those IDs really are
other than user exposed values.)

So the customer can do this:

select * from cv_episode where pid='xyz5678';

But I will say it sounds like you really need to hire a databasebrainz
entity. 8^)
(mumbles something about Codd and not exposing surrogate keys)

Ed