ki-backend-rs/model/sqlite3/migrations/2021-05-29-094109_initial_schema/up.sql

CREATE TABLE IF NOT EXISTS users (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  nickname VARCHAR,
  pronouns VARCHAR,
  address_1_name VARCHAR,
  address_2_additional VARCHAR,
  address_4_street VARCHAR,
  adress_house_number VARCHAR,
  adress_city_code VARCHAR,
  adress_country VARCHAR,
  Ehrenaemter VARCHAR,
  gravatar_email VARCHAR,
  Freitext VARCHAR
);

CREATE TABLE IF NOT EXISTS user_skills ( 
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  user_id INTEGER NOT NULL,
  skill_id INTEGER OT NULL,
  level INTEGER, 
  FOREIGN KEY(user_id)  REFERENCES users(id),
  FOREIGN KEY(skill_id) REFERENCES skills(id)
);

CREATE TABLE IF NOT EXISTS  user_languages (
  id INTEGER PRIMARY KEY AUTOINCREMENT,

  user_id INTEGER NOT NULL,
  language_id INTEGER NOT NULL,
  level INTEGER,
  FOREIGN KEY(user_id) REFERENCES users(id),
  FOREIGN KEY(language_id)  REFERENCES languages(id)
);

CREATE TABLE IF NOT EXISTS  skills (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name VARCHAR
);

CREATE TABLE IF NOT EXISTS  user_experiences (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  user_id INTEGER  NOT NULL,
  description VARCHAR,
  start date,
  end date,
  FOREIGN KEY (user_id) REFERENCES users(id)
);

CREATE TABLE IF NOT EXISTS  user_search_topics (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  user_id INTEGER NOT NULL,
  topic_id INTEGER NOT NULL,
  FOREIGN KEY (user_id) REFERENCES users(id),
  FOREIGN KEY (topic_id) REFERENCES topics(id)
);

CREATE TABLE IF NOT EXISTS  user_contacts (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  user_id INTEGER NOT NULL,
  contact_type_id INTEGER  NOT NULL,
  content_ VARCHAR,
  FOREIGN KEY(user_id) REFERENCES users(id),
  FOREIGN KEY (contact_type_id) REFERENCES contact_types(id)
);

CREATE TABLE IF NOT EXISTS  topics (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name VARCHAR
);

CREATE TABLE IF NOT EXISTS  languages (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name VARCHAR
);

CREATE TABLE IF NOT EXISTS  experiences (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name VARCHAR
);

CREATE TABLE IF NOT EXISTS  contact_types (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name VARCHAR
);