References to same table

Im writing a simple editor for a game, to manage the relationships between creatures. The creatures table is setup with a id, name etc. and a boolean is_prey column; what I need is to figure out how a) an ORM relation can be defined between id's from the same table b) how to limit the 'available' other-nodes to only include the ones marked as is_prey.

A usage scenario would be like: The creatures table contains three creatures; a shark (ID 1), a whale (ID 2) and a tuna (ID 3). The Tuna is eatable (by the shark, and marked is_prey). Now, the shark is loaded in the CMS. Checkboxes for "available prey" should appear, listi开发者_开发知识库ng only the Tuna as an option.

How would this prey map be stored (table structure)?

Thanks!

Im using the cool crud_scaffolding module with Kohana 2.3.4

I think you need two tables here, a creatures table and a table to represent the predator-prey relationship:

  CREATE TABLE creature (
    id  INT,
    name tinytext
  );

  CREATE TABLE prey (
    predator INT,
    prey INT,
    FOREIGN KEY predator REFERENCES creature (id),
    FOREIGN KEY prey REFERENCES creature (id)
  );

This way, a creatures can be prey to more than one creature.

I don't know Kohana, so you're on your own there.