Petzen Church Records Database

drop database if exists petzen;

create database if not exists petzen DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

use petzen;

# -- Church archiVes are the repositories of the (at least) digitized verisons of the Kirchenbücher of the
# -- localites/churches for which they are responsible.
# -- This table gives the first part of the Archion citation BGS-compliant path to the URL.

# -- A general table for any sort of database of digital images from, say,
# -- an archive of any other sort of repo of digital images,
# -- The path by convention with be delimited with '>' s that with be BGS-compliant
# -- digital image database paths that provide increasing detail about the image).
# -- Comments: There is some duplication in the string representations.
create table if not exists digital_dbs (
    id int not null auto_increment primary key,
    path text not null
);

insert into digital_dbs (path) values
 ("Niedersachsen > Niedersächsisches Landesarchiv > Kirchenbücher der Evangelisch - Lutherischen Landeskirche Schaumburg - Lippe > Petzen > Verzeichnis der Getrauten und Gestobenen 1641 -1784"),
 ("Niedersachsen > Niedersächsisches Landesarchiv > Kirchenbücher der Evangelisch - Lutherischen Landeskirche Schaumburg - Lippe > Petzen > Verzeichnis der Getauften und Konfirmierten 1641 -1784"),
 ("Niedersachsen > Niedersächsisches Landesarchiv > Kirchenbücher der Evangelisch - Lutherischen Landeskirche Schaumburg - Lippe > Petzen > Verzeichnis der Getauften, Konfirmierten, Getrauten und Gestorbenen 1785 -1827"
);

# -- This is the table of digital image database and the particular image in it that we cited.
# -- The BGS-compliant citation itself, the string, will be create the software that queries the database.
create table if not exists cited_images (
    id int not null auto_increment primary key,
    db_id int not null,
    image_no int not null,
    unique (db_id, image_no),
    foreign key(db_id) references digital_dbs(id)
);

# --
#-- fsid = FamilySearch Idenifier
# -- assumes the person has only one surname spelling, but can have
# -- Was their birth legitimate, illegitimate or unknown, say, do to hard to
# -- read gothic script.
# -- alternate given names -- at leasst the order or spelling of the given names
# -- legit = legitimate birht: y = yes, n = no, u = uncertain.
# -- sex_order -- the, say, 2nd son or 1st daugther.
create table if not exists persons (
    id int AUTO_INCREMENT PRIMARY KEY,
    fsid char(8) not null,
    birth_order int not null,
    sex_order int not null,
    sex ENUM('m', 'f') NOT NULL,
    legit ENUM('y', 'n', 'u'),
    surname varchar(25) NOT NULL,
    unique (fsid)
) engine = INNODB;

# -- Eventually, we don't know the father or the mother or either.
# -- These are those two " Unknown " persons.
insert into persons (
        fsid,
        birth_order,
        sex_order,
        sex,
        legit,
        surname
    ) values
    ('0000-001', 0, 0, 'm', "y", "Unknown father"),
    ('0000-002', 0, 0, 'f', "y", "Unknown mother");

# -- pastorid == pastor
# -- cid    == citation id
create table if not exists life_events (
    id INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
    date DATE NOT NULL,
    type ENUM(
        'birth',
        'baptism',
        'marriage',
        'death',
        'burial'
    ) NOT NULL,
    pastorid int not null,
    cid INT NOT NULL,
    foreign key(pastorid) references persons(id),
    foreign key(cid) references cited_images(id)
) engine = INNODB;
# --
# -- a cited_event may reference more than one person and a person
# -- may be cited more then once
create table if not exists persons_cited (
    pid INT NOT NULL,
    eid INT NOT NULL,
    foreign key (pid) references persons(id),
    foreign key (eid) references life_events(id)
) engine = INNODB;
# -- persons residences over time. The date of when they live
# -- there is recorded in the life_events tables
create table if not exists residences (
    id int AUTO_INCREMENT PRIMARY KEY,
    pid int NOT NULL,
    eid int NOT NULL,
    unique (pid, eid),
    locality VARCHAR(25) NOT NULL,
    num int NOT NULL,
    FOREIGN KEY (pid) references persons(id),
    FOREIGN KEY (eid) references life_events(id)
) engine = INNODB;
# -- Job names with an English definition
create table if not exists job_names (
    id int AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(17) NOT NULL,
    defn VARCHAR(75) NOT NULL
) engine = INNODB;
INSERT INTO `job_names` (`id`, `name`, `defn`)
VALUES (
        1,
        'Canonier',
        'canoneer;gunner;artillery gunner'
    ),
    (2, 'Einlieger', 'free agricultural laborer'),
    (
        3,
        'Taglöhner',
        'day laborer (one who works small jobs paid by the day)'
    ),
    (4, 'Leibzüchter', 'person on life estate'),
    (5, 'unknown', 'none'),
    (6, 'Küster', 'sexton, parish clerk'),
    (
        7,
        'Colon',
        'also Kolon; settler; farmer (of a crop farm with hereditary title)'
    ),
    (8, 'Pfarrer', 'pastor; priest'),
    (9, 'Schuster', 'cobbler; shoemaker'),
    (10, 'Schumacher', 'cobbler; shoemaker'),
    (11, 'Schüßler', 'bowlmaker');
# -- Jobs held, occupations had, by the person. And the event
# -- that mentions the job description.
create table if not exists jobs_held (
    id int AUTO_INCREMENT PRIMARY KEY,
    jib int NOT NULL,
    pid int NOT NULL,
    eid int NOT NULL,
    unique (jib, pid),
    unique (pid, eid),
    foreign key (jib) references job_names(id),
    foreign key (pid) references persons(id),
    foreign key (eid) references life_events(id)
) engine = INNODB;
# -- We use couples rather than parents because not all couples
# -- become parents. And not all parents are married when they
# -- have children. So couples allows us to model those who are
# -- both married and unmarried and who may or may not have
# -- had children born in or out of wedlock.
create table if not exists couples (
    id int AUTO_INCREMENT PRIMARY KEY,
    mid int NOT NULL,
    wid int NOT NULL,
    unique(mid, wid),
    foreign key (mid) references persons(id),
    foreign key (wid) references persons(id)
) engine = INNODB;
# --
# -- All children born to couples (married or unmarried).
# -- cplid == couple it
create table if not exists children (
    id int AUTO_INCREMENT PRIMARY KEY,
    cplid int not null,
    foreign key (cplid) references couples(id)
) engine = INNODB;
#-- Is this of use--maybe later on. For now, we will track
# -- participants using the persons-cited table.
# -- create table if not exists participants (
# --  pid INT NOT NULL,
# --  eid INT NOT NULL,
# --  is_principal BOOLEAN NOT NULL,
# --     foreign key (pid) references persons(id),
# --     foreign key (eid) references event(id)
# --  );
#-- Baptismal sponsors
#-- evid - baptism event
#-- pid person id
create table if not exists sponsors (
    eid INT NOT NULL,
    pid INT NOT NULL,
    foreign key (eid) references life_events(id),
    foreign key (pid) references persons(id)
) engine = INNODB;