/*******************************************************
 * (c) 2005 Juraj Kolesar (koli@koli.sk)							 *
 * modified from DBDesgner4 automaticly generated file *
 *******************************************************/

SET NAMES utf8;
CREATE DATABASE tucniak;
USE tucniak;

/* PERSON {{{ */
CREATE TABLE c_person ( # {{{
  pk_c_person INT NOT NULL AUTO_INCREMENT,
  name VARCHAR(50) NOT NULL DEFAULT "",
  surname VARCHAR(50) NOT NULL DEFAULT "",
  nickname VARCHAR(50) NOT NULL DEFAULT "",
  gender ENUM ("M","F") NULL,
  title_preffix VARCHAR(20) NOT NULL DEFAULT "",
  title_suffix VARCHAR(10) NOT NULL DEFAULT "",
  PRIMARY KEY(pk_c_person)
); # }}}
CREATE TABLE c_join_person_account ( # {{{
  pk_c_join_person_account INT NOT NULL AUTO_INCREMENT,
  fk_c_person INT NOT NULL,
  fk_c_account INT NOT NULL,
  priority ENUM ("P","S") NULL DEFAULT "S",
  note VARCHAR(50) NULL,
  PRIMARY KEY(pk_c_join_person_account)
); # }}}
CREATE TABLE c_join_person_address ( # {{{
  pk_c_join_person_address INT NOT NULL AUTO_INCREMENT,
  fk_c_person INT NOT NULL,
  fk_c_address INT NOT NULL,
  priority ENUM ("P","S") NULL DEFAULT "S",
  note VARCHAR(50) NULL,
  PRIMARY KEY(pk_c_join_person_address)
); # }}}
CREATE TABLE c_join_person_email ( # {{{
  pk_c_join_person_email INT NOT NULL AUTO_INCREMENT,
  fk_c_person INT NOT NULL,
  fk_c_email INT NOT NULL,
  priority ENUM ("P","S") NULL DEFAULT "S",
  note VARCHAR(50) NULL,
  PRIMARY KEY(pk_c_join_person_email)
); # }}}
CREATE TABLE c_join_person_phone ( # {{{
  pk_c_join_person_phone INT NOT NULL AUTO_INCREMENT,
  fk_c_person INT NOT NULL,
  fk_c_phone INT NOT NULL,
  priority ENUM ("P","S") NOT NULL DEFAULT "S",
  note VARCHAR(50) NULL,
  PRIMARY KEY(pk_c_join_person_phone)
); # }}}
CREATE TABLE c_join_person_web ( # {{{
  pk_c_join_person_web INT NOT NULL AUTO_INCREMENT,
  fk_c_person INT NOT NULL,
  fk_c_web INT NOT NULL,
  priority ENUM ("P","S") NULL DEFAULT "S",
  note VARCHAR(50) NULL,
  PRIMARY KEY(pk_c_join_person_web)
); # }}}
/* }}} */ 
/* COMPANY {{{ */
CREATE TABLE c_company ( #{{{
  pk_c_company INT NOT NULL AUTO_INCREMENT,
  name VARCHAR(50) NOT NULL DEFAULT "",
  idn VARCHAR(20) NOT NULL DEFAULT "",
  txn VARCHAR(20) NOT NULL DEFAULT "",
  fk_c_company_type INT NOT NULL DEFAULT 1,
  PRIMARY KEY(pk_c_company)
); #}}}
CREATE TABLE c_company_type ( #{{{
  pk_c_company_type INT NOT NULL AUTO_INCREMENT,
  name VARCHAR(20) NOT NULL,
  PRIMARY KEY(pk_c_company_type)
); #}}}
CREATE TABLE c_department ( #{{{
  pk_c_department INT NOT NULL AUTO_INCREMENT,
  fk_c_company INT NOT NULL,
  name VARCHAR(50) NULL,
  PRIMARY KEY(pk_c_department)
); #}}}
CREATE TABLE c_join_company_account ( #{{{
  pk_c_join_company_account INT NOT NULL AUTO_INCREMENT,
  fk_c_company INT NOT NULL,
  fk_c_account INT NOT NULL,
  priority ENUM ("P","S") NOT NULL DEFAULT "S",
  note VARCHAR(50) NULL,
  PRIMARY KEY(pk_c_join_company_account)
); #}}}
CREATE TABLE c_join_company_address ( #{{{
  pk_c_join_company_address INT NOT NULL AUTO_INCREMENT,
  fk_c_address INT NOT NULL,
  fk_c_company INT NOT NULL,
  priority ENUM ("P","S") NULL DEFAULT "S",
  note VARCHAR(50) NULL,
  PRIMARY KEY(pk_c_join_company_address)
); #}}}
CREATE TABLE c_join_company_email ( #{{{
  pk_c_join_company_email INT NOT NULL AUTO_INCREMENT,
  fk_c_company INT NOT NULL,
  fk_c_email INT NOT NULL,
  priority ENUM ("P","S") NULL DEFAULT "S",
  note VARCHAR(50) NULL,
  PRIMARY KEY(pk_c_join_company_email)
); #}}} 
CREATE TABLE c_join_company_phone ( #{{{
  pk_c_join_company_phone INT NOT NULL AUTO_INCREMENT,
  fk_c_phone INT NOT NULL,
  fk_c_company INT NOT NULL,
  priority ENUM ("P","S") NULL DEFAULT "S",
  note VARCHAR(50) NULL,
  PRIMARY KEY(pk_c_join_company_phone)
); #}}}
CREATE TABLE c_join_company_web ( #{{{
  pk_c_join_company_web INT NOT NULL AUTO_INCREMENT,
  fk_c_company INT NOT NULL,
  fk_c_web INT NOT NULL,
  priority ENUM ("P","S") NULL DEFAULT "S",
  note VARCHAR(50) NULL,
  PRIMARY KEY(pk_c_join_company_web)
); #}}}
CREATE TABLE c_join_employ ( #{{{
  pk_c_join_employ INT NOT NULL AUTO_INCREMENT,
  fk_c_company INT NOT NULL,
  fk_c_person INT NOT NULL,
  fk_c_department INT NOT NULL,
  note VARCHAR(255) NULL,
  PRIMARY KEY(pk_c_join_employ, fk_c_company, fk_c_person)
); #}}}
/* }}} */
/* SECONDARY DATA {{{ */
CREATE TABLE c_account ( #{{{
  pk_c_account INT NOT NULL AUTO_INCREMENT,
  fk_c_bank INT NULL,
  number VARCHAR(50) NULL,
  PRIMARY KEY(pk_c_account)
); #}}}
CREATE TABLE c_bank ( #{{{
  pk_c_bank INT NOT NULL AUTO_INCREMENT,
  name VARCHAR(50) NULL,
  code VARCHAR(4) NULL,
  PRIMARY KEY(pk_c_bank)
); #}}}
CREATE TABLE c_address ( #{{{
  pk_c_address INT NOT NULL AUTO_INCREMENT,
  street VARCHAR(50) NULL,
  city VARCHAR(50) NULL,
  zip VARCHAR(50) NULL,
  state VARCHAR(50) NULL,
  PRIMARY KEY(pk_c_address)
); #}}}
CREATE TABLE c_email ( #{{{
  pk_c_email INT NOT NULL AUTO_INCREMENT,
  url VARCHAR(50) NULL,
  PRIMARY KEY(pk_c_email)
); #}}}
CREATE TABLE c_web ( #{{{
  pk_c_web INT NOT NULL AUTO_INCREMENT,
  url VARCHAR(50) NULL,
  PRIMARY KEY(pk_c_web)
); #}}}
CREATE TABLE c_phone ( #{{{
  pk_c_phone INT NOT NULL AUTO_INCREMENT,
  number VARCHAR(20) NULL,
  PRIMARY KEY(pk_c_phone)
); #}}}
/* }}} */

/* COMPANY TYPE DUMP {{{ */
INSERT INTO c_company_type (name) VALUES ('iné');
INSERT INTO c_company_type (name) VALUES ('s.r.o.');
INSERT INTO c_company_type (name) VALUES ('a.s.');
INSERT INTO c_company_type (name) VALUES ('n.o.');
INSERT INTO c_company_type (name) VALUES ('szčo');
/* }}} */