What is an External Key?

[royw]

Howdy,

I've been trying to figure out what is meant by "external key" in LMCE's databases (EK_* attributes).  This wiki page looks cut off before getting to the meat:

  http://wiki.linuxmce.org/index.php/Naming_of_Foreign_Keys%2C_Indirect_Keys%2C_External_Keys

Googling around has left me a little confused.  Is it just a table extension where the external key matches the primary key of the original table with a 1:1 mapping between tables?  Is it a substitution or surrogate key?

Any hints on where to look?

TIA,
Roy

[Matthew]

Howdy,

I've been trying to figure out what is meant by "external key" in LMCE's databases (EK_* attributes).  This wiki page looks cut off before getting to the meat:

  http://wiki.linuxmce.org/index.php/Naming_of_Foreign_Keys%2C_Indirect_Keys%2C_External_Keys

Googling around has left me a little confused.  Is it just a table extension where the external key matches the primary key of the original table with a 1:1 mapping between tables?  Is it a substitution or surrogate key?

An "external key" in a database is a key to a table (eg. "tableA") that is stored in a separate table (eg. "tableE") , with the data in the tableA key field just a reference to the actual key datat in the corresponding tableE field. It's like a foreign key, but the key is not a field in a separate table that contains any other data than the keys. It's a way of normalizing tableA even further, so tableA keys can be manipulated without referencing tableA at all. Reindexing keys doesn't have to access tableA, nor does changing the keys. Performance can improve if the keys are changed relatively often, compensating for the extra lookup to the external table when the keys are read. And programs can safely access the key table without touching the rest of the data associated with the key, for access control or just risk management (eg. if keys are expendable but the rest of the data isn't, an access to the key that could corrupt its table can be limited to just the key table, not the rest of the data that uses those external keys). External keys also allow techniques where multiple data tables each use the same external key table, for performance, consistency and other reasons.

[royw]

Thank you.  That is a good explanation of indirect keys.  But I don't think that is how the external keys are being using in pluto_media.

pluto_media uses these external keys:

  EK_CommandGroup_Start
  EK_CommandGroup_Stop
  EK_Country
  EK_DesignObj_Popup
  EK_Device
  EK_Device_Ripping
  EK_MediaType
  EK_Package
  EK_Screen_Order
  EK_Users
  EK_Users_Private

I think these are the correct external mappings:

  pluto_media.*.EK_Country -> pluto_main.Country.PK_Country
  pluto_media.*.EK_Device -> pluto_main.Device.PK_Device
  pluto_media.*.EK_MediaType -> pluto_main.MediaType.PK_MediaType
  pluto_media.*.EK_Package -> pluto_main.Package.PK_Package
  pluto_media.*.EK_Users -> pluto_main.Users.PK_Users

The ones that I haven't matched look like they ought to be join tables but while the first side exists in pluto_main, the second side doesn't exist in any of the pluto databases.  For example: EK_CommandGroup_Start, there is a pluto_main.CommandGroup table but there is not a Start table.

  EK_CommandGroup_Start
  EK_CommandGroup_Stop
  EK_DesignObj_Popup
  EK_Device_Ripping
  EK_Screen_Order
  EK_Users_Private
 
There is some mechanism here that I just don't understand.

FYI, why I'm trying to dig so deeply is that I'm attempting to create ActiveRecord database models for pluto_media, which is the first step of writing a rails application to manage the media.

Thank you,
Roy

[hari]

say if you have the fields:
EK_CommandGroup_Start

then pluto tries to match a table "CommandGroup_Start". When that is not found it strips the last occurence of _(.*) and tries to match CommandGroup in this case.

So this is the way it handles multiple EK's to the same destination in a single table.

hope that helps,
best regards,
Hari

[royw]

Great!  Thank you Hari!