Notes on my Relatives MySQL Database

   default-character-set=latin1
   default-collation=latin1_swedish_ci

Table Creation

   mysql -psqlpw < create_people_tables.sql

create_people_tables.sql
use relatives;

# Drop the people table if it exists, then recreate it

DROP TABLE IF EXISTS people;
CREATE TABLE people
(     # Security Field Codes:  0=Open to Anybody.  1=Open to Family Only.  2=Open to Rick Only.
  uid            int      not null                comment 'Index into homes & addresses tables',
  first          char(20) not null                comment 'First name',
  middle         char(20)     null default null   comment 'Middle Name',
  sec1           char(1)      null default null   comment 'Middle Name Security',
  last           char(20) not null                comment 'Last Name',
  sec2           char(1)      null default '1'    comment 'Last Name Security',
  full_name      char(30) not null                comment 'Preferred Full Name',
  passwd         char(10)     null default null   comment 'Authentication String',
  mom            int          null default null   comment 'Index into People Table',
  dad            int          null default null   comment 'Index into People Table',
  current_aid    int          null default null   comment 'Index into Address Table',
  sec3           char(1)      null default '1'    comment 'Current Address Security',
  home_phone     char(14)     null default null   comment '(xxx) xxx-xxxx',
  sec4           char(1)      null default '1'    comment 'Home Phone Security',
  cell_phone     char(14)     null default null   comment '(xxx) xxx-xxxx',
  sec5           char(1)      null default '1'    comment 'Cell Phone Security',
  work_phone     char(14)     null default null   comment '(xxx) xxx-xxxx',
  sec6           char(1)      null default '1'    comment 'Work Phone Security',
  email          char(40)     null default null   comment 'Preferred E-mail Address',
  sec7           char(1)      null default '1'    comment 'E-mail Security',
  birth          date         null default null   comment 'Birth Date',
  sec8           char(1)      null default null   comment 'Birth Year Security',
  b_city         char(20)     null default null   comment 'Birth City',
  b_state        char(2)      null default null   comment 'Birth State',
  b_country      char(2)      null default 'US'   comment '2-character Country Code',
  sec9           char(1)      null default '1'    comment 'Birth Location Security',
  sex            char(1)      null default null   comment 'M or F',
  spouse         int          null default null   comment 'Index into People Table',
  anniversary    date         null default null   comment 'Wedding Anniversary',
  sec10          char(1)      null default '1'    comment 'Wedding Anniversary Security',
  death          date         null default null   comment 'Death Date',
  sec11          char(1)      null default null   comment 'Death Date Security',
  website        char(40)     null default null   comment 'Personal Website',
  sec12          char(1)      null default '1'    comment 'Personal Website Security',
  im_service     smallint     null default null   comment 'IM Service Provider 1-3=AOL,Windows,Yahoo',
  im_screenname  char(30)     null default null   comment 'IM Screenname',
  sec13          char(1)      null default '1'    comment 'IM Security',
  last_update    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  primary key (uid)
) comment='Everything Unique about a Person';


DROP TABLE IF EXISTS homes;
CREATE TABLE homes
(
  uid            int      not null                comment 'People Index',
  seq            int      not null auto_increment comment 'Sequence Number',
  aid            int      not null                comment 'Index into Addresses Table',
  from_date      date         null default null   comment 'Start Date (if known)',
  to_date        date         null default null   comment 'End Date (if known)',
  primary key (uid,seq)
) comment='Homes are unique for each person and sequential';


DROP TABLE IF EXISTS addresses;
CREATE TABLE addresses
(
  aid            int      not null                comment 'Index into addresses Table',
  street_address char(30) not null                comment 'Street Address',
  city           char(15) not null                comment 'City',
  state          char(2)  not null                comment 'State',
  zipcode        char(5)  not null                comment 'Zip Code',
  country        char(2)  not null default 'US'   comment '2-character Country Code',
  primary key (aid)
) comment='Addresses are shared between people';

   mysql -psqlpw < populate_db.sql

   INSERT INTO
people (uid,  first     ,  middle   ,  last     , full_name  , current_aid,  home_phone  ,   birth    ,sex)
values ( 1,'Richard'    ,'Alan'     ,'Jasper'   ,'Rick Jasper'    ,    1,'(408) 227-2856','1955-04-22','M'),
       ( 2,'Katherine'  ,'Ann'      ,'Jasper'   ,'Kathy Jasper'   ,    1,'(408) 227-2856','19xx-06-20','F'),
       ( 3,'Melanie'    ,'Lynne'    ,'Jasper'   ,'Melanie Jasper' ...
...
update people set mom=39,dad=38,spouse=2,anniversary='1989-03-11',email='rickajasper@yahoo.com' where uid=1;
update people set cell_phone='(408) 398-9065' where uid=1;
...
INSERT INTO homes (uid,seq,aid) values (1,40,1),(2,60,1),(3,2,1),(4,2,1),
    ...
INSERT INTO addresses (aid,street_address,city,state,zipcode)
        values (1,'139 Farnham Court','San Jose','CA','95139'),
               (2,'6922 ...