Best to think of tables as models of real-world entities. In the case of lineage, think of a table as a person.
CREATE TABLE person (
-- Start with an identity column. I used a bigint because there are lots of people
-- out there... auto-increment means it will automatically increase 1, 2, 3, etc..
-- as you add records to the table. Primary key means it's unique, indexed, and
-- the identity column of this table.
id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
-- First, Middle, Last is a western convention for names, but suits most table
-- designs. You could go with name/surname if you want to get technical
first_name VARCHAR(255) NOT NULL,
middle_name VARCHAR(255) NULL,
last_name VARCHAR(255) NOT NULL,
-- Gender can simply be a CHAR, but can also reference
-- A gender table (which can also be indexed by a char)
gender_id CHAR(1) NOT NULL DEFAULT 'M',
-- DOB can just be a date field
dob DATE NULL,
-- Father and Mother reference other person records.
-- This allows a recursive tree whereby we can search infinite
-- chains of relationships. Children of a record will always have
-- a father id or mother id referencing the parent.
father_id BIGINT NULL REFERENCES person (id),
mother_id BIGINT NULL REFERENCES person (id),
created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
) TYPE=InnoDB;
A point of note… you may prefer instead of the single-table approach to go with a multi-table approach, e.g.:
-- Note no father_id or mother_id columns
CREATE TABLE person (
id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
first_name VARCHAR(255) NOT NULL,
middle_name VARCHAR(255) NULL,
last_name VARCHAR(255) NOT NULL,
gender_id CHAR(1) NOT NULL DEFAULT 'M',
dob DATE NULL,
created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
) TYPE=InnoDB;
-- This table models parent-child relationships
-- One person can have multiple parents, one parent can have multiple children
-- This is a traditional many-to-many composite-key relationship
CREATE TABLE person_parent (
person_id BIGINT NOT NULL REFERENCES person (id),
parent_id BIGINT NOT NULL REFERENCES person (id)
) TYPE=InnoDB;
The advantage of this type of approach is multi-faceted. First, it would allow a simple join to find all children of a parent, and wouldn’t complicate matters of gender implication in the “Mother” and “Father” roles defined in the single-table approach. E.g., what if a person has 2 mothers (non-traditional household). And why should role even be implied in the relationship? “Parent” seems to be enough (no need to store “father” / “mother” role information… parent’s gender should be enough for most databases, without getting too personal).
So there you have it! That’s a start… I’m sure you’re gonna have a lot more questions… it’s a complicated DB structure to use.
