Can a database index span tables? I'm on Postgres

Question

Is it possible to create an index that has as one of its columns values from another table?

Example:

model Pet
  primary_key id
  foreign_key Species
end
model Species
  primary_key id
  int genus
end

Assume there's a lot of kinds of species, with fewer types of genuses. I'd like to create an index on the Pets table by Genus. Can it be done?

If so, I'd be extra grateful if you could point me in the right direction on how to do it in Rails migration.

Answer 1

No. In any relational database technology, "index" means an index of the table. You could instead combine the two like this:

ANIMAL_CLASS
 + name
 + id
 + LEVEL
 + parent_id

Where level is logically an enumeration of {SPECIES, GENUS, .... }