DROP TABLE IF EXISTS company;

CREATE TABLE company 
(
    id int(10) NOT NULL auto_increment,

    title varchar(255) NOT NULL,

    PRIMARY KEY (id),
    UNIQUE KEY (title)
);

DROP TABLE IF EXISTS vacancy;

CREATE TABLE vacancy 
(
    id int(10) NOT NULL auto_increment,
    company_id int(10) NOT NULL,

    start_date datetime,
    up_date datetime,
    salary int(10),
    salary_to int(10),
    title varchar(255) NOT NULL,
    text varchar(1024) NOT NULL,

    PRIMARY KEY (id),
    KEY (company_id)
);

DROP TABLE IF EXISTS keyword;

CREATE TABLE keyword 
(
    id int(10) NOT NULL auto_increment,

    keyword char(20) NOT NULL,

    PRIMARY KEY (id),
    UNIQUE KEY (keyword)
);

DROP TABLE IF EXISTS vacancy_keyword;

CREATE TABLE vacancy_keyword 
(
    vacancy_id int(10) NOT NULL,
    keyword_id int(10) NOT NULL,

    UNIQUE KEY (vacancy_id, keyword_id),
    UNIQUE KEY (keyword_id, vacancy_id)
);
