philippebeck/nens

DROP DATABASE IF EXISTS nens;
CREATE DATABASE nens CHARACTER SET 'utf8';

USE nens;

CREATE TABLE Articles(
  id        SMALLINT      UNSIGNED  PRIMARY KEY AUTO_INCREMENT,
  name      VARCHAR(250)  NOT NULL  UNIQUE,
  text      TEXT          NOT NULL  UNIQUE,
  image     VARCHAR(250)  NOT NULL  UNIQUE,
  alt       VARCHAR(250)  NOT NULL,
  url       VARCHAR(250),
  likes     TEXT          NOT NULL,
  cat       VARCHAR(25)   NOT NULL,
  createdAt DATETIME      NOT NULL,
  updatedAt DATETIME      NOT NULL)
ENGINE=INNODB DEFAULT CHARSET=utf8;

CREATE TABLE Galleries(
  id      SMALLINT      UNSIGNED  PRIMARY KEY AUTO_INCREMENT,
  name    VARCHAR(250)  NOT NULL  UNIQUE,
  author  VARCHAR(250)  NOT NULL,
  cover   VARCHAR(250)  NOT NULL  UNIQUE)
ENGINE=INNODB DEFAULT CHARSET=utf8;

CREATE TABLE Images(
  id          SMALLINT      UNSIGNED    PRIMARY KEY  AUTO_INCREMENT,
  name        VARCHAR(250)  NOT NULL    UNIQUE,
  description VARCHAR(250)  NOT NULL,
  galleryId   SMALLINT      UNSIGNED    NOT NULL,
  CONSTRAINT  fk_galleryId  FOREIGN KEY (galleryId)  REFERENCES Galleries(id))
ENGINE=INNODB DEFAULT CHARSET=utf8;

CREATE TABLE Links(
  id    SMALLINT      UNSIGNED  PRIMARY KEY AUTO_INCREMENT,
  name  VARCHAR(250)  NOT NULL  UNIQUE,
  url   VARCHAR(250)  NOT NULL  UNIQUE,
  cat   VARCHAR(25)   NOT NULL)
ENGINE=INNODB DEFAULT CHARSET=utf8;

CREATE TABLE Products(
  id          SMALLINT      UNSIGNED  PRIMARY KEY AUTO_INCREMENT,
  name        VARCHAR(250)  NOT NULL  UNIQUE,
  description TEXT          NOT NULL  UNIQUE,
  image       VARCHAR(250)  NOT NULL  UNIQUE,
  alt         VARCHAR(250)  NOT NULL,
  price       DECIMAL       NOT NULL,
  options     TEXT          NOT NULL,
  cat         VARCHAR(25)   NOT NULL)
ENGINE=INNODB DEFAULT CHARSET=utf8;

CREATE TABLE Projects(
  id          SMALLINT      UNSIGNED  PRIMARY KEY AUTO_INCREMENT,
  name        VARCHAR(250)  NOT NULL  UNIQUE,
  description TEXT          NOT NULL  UNIQUE,
  image       VARCHAR(250)  NOT NULL  UNIQUE,
  alt         VARCHAR(250)  NOT NULL,
  url         VARCHAR(250),
  cat         VARCHAR(25)   NOT NULL,
  createdAt   DATETIME      NOT NULL,
  updatedAt   DATETIME      NOT NULL)
ENGINE=INNODB DEFAULT CHARSET=utf8;

CREATE TABLE Users(
  id        SMALLINT      UNSIGNED  PRIMARY KEY AUTO_INCREMENT,
  name      VARCHAR(250)  NOT NULL  UNIQUE,
  email     VARCHAR(250)  NOT NULL  UNIQUE,
  image     VARCHAR(250)  NOT NULL  UNIQUE,
  pass      VARCHAR(250)  NOT NULL,
  role      VARCHAR(25)   NOT NULL,
  createdAt DATETIME      NOT NULL,
  updatedAt DATETIME      NOT NULL)
ENGINE=INNODB DEFAULT CHARSET=utf8;

CREATE TABLE Orders(
  id          SMALLINT    UNSIGNED    PRIMARY KEY AUTO_INCREMENT,
  products    TEXT          NOT NULL,
  total       DECIMAL       NOT NULL,
  paymentId   VARCHAR(250)  NOT NULL    UNIQUE,
  status      VARCHAR(25)   NOT NULL,
  userId      SMALLINT      UNSIGNED    NOT NULL,
  createdAt   DATETIME      NOT NULL,
  updatedAt   DATETIME      NOT NULL,
  CONSTRAINT  fk_userId     FOREIGN KEY (userId)    REFERENCES Users(id))
ENGINE=INNODB DEFAULT CHARSET=utf8;