References to same table

Question

Hi,

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, listing 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

Answer 1

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.