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