CREATE TABLE users
(
  id int4 NOT NULL PRIMARY KEY,
  ids text,
  name text NOT NULL,
  vorname text NOT NULL,
  private bool NOT NULL,
  uni_id int4 REFERENCES unis (id),
  image_url text
);
CREATE TABLE user_data
(
  id int4 NOT NULL PRIMARY KEY REFERENCES users (id),
  uni_year int2,
  home_zip int4,
  home_town text,
  home_country text,
  relationship int2 REFERENCES relationships (id),
  political int2 REFERENCES political (id),
  major int4 REFERENCES major (id),
  ext_friends int2,
  uni_friends int2,
  icq int8,
  skype text,
  aim text,
  jabber text,
  tel text,
  street text,
  homepage text,
  school int4 REFERENCES schools (id),
  jobtype int4 REFERENCES jobs (id),
  residence int4 REFERENCES residences (id),
  room text,
  status text,
  interests text,
  music text,
  books text,
  movies text,
  "quote" text,
  about text,
  company text,
  doing_what text,
  career text,
  clubs text,
  birthdate date,
  register_date date,
  last_update date,
  sex int2
);

CREATE TABLE concentration_members
(
  concentration_id int4 REFERENCES concentrations (id),
  user_id int4 REFERENCES users (id)
);

CREATE TABLE concentrations
(
  id int4 NOT NULL PRIMARY KEY,
  name text
);

CREATE TABLE course_members
(
  course_id int4 REFERENCES courses (id),
  user_id int4 REFERENCES users (id)
);

CREATE TABLE courses
(
  id int4 NOT NULL PRIMARY KEY,
  turnus varchar(1) NOT NULL CHECK (turnus::text = 'W'::text OR turnus::text = 'S'::text),
  name text,
  "year" varchar(5) NOT NULL
);

CREATE TABLE friends
(
  id1 int4 REFERENCES users (id),
  id2 int4 REFERENCES users (id)
);

CREATE TABLE group_members
(
  group_id int4 REFERENCES groups (id),
  user_id int4 REFERENCES users (id)
);

CREATE TABLE groups
(
  id int4 NOT NULL PRIMARY KEY,
  name text,
  ids varchar(8)
);

CREATE TABLE jobs
(
  id int4 NOT NULL PRIMARY KEY,
  name text
);

CREATE TABLE lookingfor
(
  id int4 NOT NULL PRIMARY KEY,
  name text
);

CREATE TABLE lookingfor_members
(
  lookingfor_id int4,
  user_id int4 REFERENCES users (id)
);

CREATE TABLE major
(
  id int4 NOT NULL PRIMARY KEY,
  name text
);

CREATE TABLE political
(
  id int4 NOT NULL PRIMARY KEY,
  name text
);

CREATE TABLE relationships
(
  id int4 NOT NULL PRIMARY KEY,
  name text
);

CREATE TABLE residences
(
  id int4 NOT NULL PRIMARY KEY,
  name text
);

CREATE TABLE schools
(
  id int4 NOT NULL PRIMARY KEY,
  name text,
  "location" text
);

CREATE TABLE unis
(
  id int4 NOT NULL PRIMARY KEY,
  name text
);