I've been working for a few hours on a little database to document the pedigree and ownership of monsters in a game I've been playing. I wrote the tables and made forms and whatnot, but the pedigree part is the one getting my knickers in a bunch. I have 4 fields, Mate, Female Parent, Male Parent, and Child. I want to make the table refer to itself for these values, and I've searched on the internet and various forums but I can't find anything that makes sense. I'm not exactly the most experienced databaser, so if you guys can help, please don't go all technical on me with the terms. Relationship table attached.
What kind of monsters are we talking here? I would imagine many of them could come into being through what humans might consider to be "unconventional" arrangements. Do male and female parents always apply? What if they're hatched or conjured via some arcane ritual? Or perhaps there is a queen who has a novel way of fertilizing eggs.
You can infer children by looking for any monsters referencing the "parent" monster.
Ahh, sorry about that. Monsters can be either male or female, no exceptions. In the game you can breed monsters together by leaving them at an altar, after which they leave an egg which you can hatch later at your whim. Each monster must have one female and one male parent.
Alrighty, if there is a guarantee of exactly one female and exactly one male parent for each and every monster, then it is workable to include a male_parent_id and female_parent_id which both reference the primary key of your Monsters table.
Getting that information backout is a hassle. Thankfully (or unfortunately, depending on how you look at it), it is a very common hassle.
Google around for "recursive database relationship" for a whole scad of different ideas and approaches to this problem. Another term might be "hierarchical relationships", though that will be more likely to yield results for the application world and/or vendor-specific implementations that don't apply to you.