create table users (
    user_key varchar(35)  binary not null primary key,
    gender   integer      not null,
    nickname varchar(100) not null,
    interest varchar(500) not null,
    bio      varchar(500) not null,
    bday     varchar(10)  ,
    updated  timestamp,
    created  timestamp
) engine=innodb;

create table openids (
    openid_url  varchar(255) not null primary key,
    user_key    varchar(35) binary,
    updated     timestamp,
    created     timestamp,
    foreign key ( user_key ) references users(user_key) on delete cascade
) engine=innodb;

create table lovers (
    lover_id    integer not null auto_increment primary key,
    lover_name varchar(255) not null,
    updated     timestamp,
    created     timestamp
) engine=innodb;

create table my_lovers (
    lover_id    integer not null ,
    user_key    varchar(35)  binary not null,
    lover_name varchar(255) not null, -- 助長化
    sex_count   integer not null default 0,
    foreign key ( user_key ) references users(user_key) on delete cascade,
    foreign key ( lover_id ) references lovers( lover_id ) on delete cascade,
    updated     timestamp,
    created     timestamp,
    primary key( user_key , lover_id )
) engine=innodb;

create table records (
    record_id   integer not null auto_increment primary key,
    lover_id    integer not null ,
    user_key    varchar(35)  binary not null,
    location    varchar(255) not null,
    overview    text,
    todo        text,
    updated     timestamp,
    created     timestamp,
    foreign key ( user_key ) references users(user_key) on delete cascade,
    foreign key ( lover_id ) references lovers( lover_id ) on delete cascade
) engine=innodb;

create table sexual_plays(
    sexual_play_id  integer   not null auto_increment primary key,
    record_id       integer   not null,
    timeline        varchar(300) not null,
    user_key        varchar(25) binary not null,
    updated         timestamp,
    created         timestamp,
    foreign key ( record_id ) references records(record_id) on delete cascade,
    foreign key ( user_key )  references users(user_key) on delete cascade
) engine=innodb;