Genealogy and Family history

[SerbanSpire]

Hello there. I am creating a game with a strong emphasis on Family Tree similar to Crusader Kings. How would such a database look like written in mysql? Where should I start? What columns should I focus on? Thanks in advance.

[Xaos]

Make a son, daughter, mother, father, sister, brother category in the database. that covers all basis and attach those to each person.

[Callan S.]

I thought you'd just have an entry for each, with two 'son/daughter of' entry (two entries, one for each parent). Simple as that.

Everything else is just a search criteria.

[MikuzA]

How about...

UID INT NOT NULL
PUID INT

UID = UserID
PUID = ParentUserID

UID would identify the user, that has its own table for username, etc data.
PUID is just a reference towards the same table but handled as 'parents for this user'.

So as row examples,

UID PUID
1 0
2 0
3 1
3 2
4 3
5 3
6 3
7 5

UserID 1 = Has no parents or unknown.
UserID 2 = Has no parents or unknown.
UserID 3 = Child of UID 1 and UID 2.
UserID 4 = Other parent is UID 3
UserID 5 = Other parent is UID 3, and brother of 4.
UserID 6 = Other parent is UID 3, and sister of 4 and 5.
UserID 7 = Other parent is UID 5, making UID 3 as grandpa!

Just an idea on how to simply proceed with this.

[Winawer]

Running queries on that type of structure is a pain, though, so you'll probably need to use some sort of trick (breadcrumbs, for example). Depending on the actual requirements, of course (can't remember what the tree was like in Crusader Kings).

[Xaos]

I thought you'd just have an entry for each, with two 'son/daughter of' entry (two entries, one for each parent). Simple as that.

Everything else is just a search criteria.

Yup. Do this. The rest is unnecessary (disregard my post) as a check for anyone (brother, sister, aunt, uncle, cousin) can be run by the parents id