Probleem

We maken de database en de tabellen niet aan met behulp van een visuele designer maar met behulp van SQL scripts.

Design

Vooraleer de database of een tabel te maken gaan we na als die al bestaat. Indien dit het geval is, deleten we eerst de tabel.

Oplossing

Globals instellen (alleen op je lokale MySQL Server, niet op die van de school)

-- An Orm Apart -- Tuesday 20th of April 2021 03:30:55 PM
--
SET GLOBAL TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- mode changes syntax and behavior to conform more closely to standard SQL.
-- It is one of the special combination modes listed at the end of this section.
SET GLOBAL sql_mode = 'ANSI';

Database maken (alleen op je lokale MySQL Server, niet op die van de school) en activeren

-- If database does not exist, create the database
CREATE DATABASE IF NOT EXISTS Vos1;
USE `Vos1`;

Foreign Key constraints opheffen

-- An Orm Apart -- Tuesday 27th of April 2021 09:54:22 AM
-- 
-- With the MySQL FOREIGN_KEY_CHECKS variable,
-- you don't have to worry about the order of your
-- DROP and CREATE TABLE statements at all, and you can
-- write them in any order you like, even the exact opposite.
SET FOREIGN_KEY_CHECKS = 0;

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql: CREATE TABLE Organisation
-- Created on Tuesday 27th of April 2021 09:54:22 AM
-- 
DROP TABLE IF EXISTS `Organisation`;
CREATE TABLE `Organisation` (
	`Name` NVARCHAR (255) NOT NULL,
	`Street` NVARCHAR (255) NOT NULL,
	`PostalCode` VARCHAR (20) NOT NULL,
	`City` NVARCHAR (80) NOT NULL,
	`Latitude` DECIMAL(7, 5) NOT NULL,
	`Longitude` DECIMAL(8, 5) NOT NULL,
	`Mobile` VARCHAR (25) NOT NULL,
	`Id` INT NOT NULL AUTO_INCREMENT,
	CONSTRAINT PRIMARY KEY(`Id`),
	CONSTRAINT uc_Organisation_Name UNIQUE (Name));

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql: CREATE TABLE Log
-- Created on Tuesday 27th of April 2021 09:54:22 AM
-- 
DROP TABLE IF EXISTS `Log`;
CREATE TABLE `Log` (
	`UserName` NVARCHAR (50) NOT NULL,
	`Email` NVARCHAR (255) NOT NULL,
	`Role` NVARCHAR (50) NOT NULL,
	`ProcedureCode` NVARCHAR (25) NOT NULL,
	`ProcedureTitle` NVARCHAR (255) NOT NULL,
	`StepTitle` VARCHAR (255) NOT NULL,
	`ActionCode` NVARCHAR (10) NOT NULL,
	`CallNumber` VARCHAR (25) NOT NULL,
	`SendNumber` VARCHAR (25) NOT NULL,
	`Id` INT NOT NULL AUTO_INCREMENT,
	CONSTRAINT PRIMARY KEY(`Id`),
	`InsertedOn` TIMESTAMP NULL);

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql: CREATE TABLE User
-- Created on Tuesday 27th of April 2021 09:54:22 AM
-- 
DROP TABLE IF EXISTS `User`;
CREATE TABLE `User` (
	`Name` NVARCHAR (50) NOT NULL,
	`Salt` NVARCHAR (255) NULL,
	`Password` NVARCHAR (255) NULL,
	`PersonId` INT NULL,
	`RoleId` INT NULL,
	`Id` INT NOT NULL AUTO_INCREMENT,
	CONSTRAINT PRIMARY KEY(`Id`),
	`InsertedOn` TIMESTAMP NULL,
	CONSTRAINT uc_User_Name UNIQUE (Name),
	CONSTRAINT fk_UserPersonId FOREIGN KEY (`PersonId`) REFERENCES `Person` (`Id`),
	CONSTRAINT fk_UserRoleId FOREIGN KEY (`RoleId`) REFERENCES `Role` (`Id`));

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql: CREATE TABLE Person
-- Created on Tuesday 27th of April 2021 09:54:22 AM
-- 
DROP TABLE IF EXISTS `Person`;
CREATE TABLE `Person` (
	`FirstName` NVARCHAR (50) NOT NULL,
	`LastName` NVARCHAR (100) NOT NULL,
	`PhoneWork` VARCHAR (25) NULL,
	`PhoneHome` VARCHAR (25) NULL,
	`Mobile` VARCHAR (25) NOT NULL,
	`Email` NVARCHAR (255) NOT NULL,
	`Street` NVARCHAR (255) NULL,
	`PostalCode` VARCHAR (20) NULL,
	`City` NVARCHAR (80) NULL,
	`Function` NVARCHAR (50) NOT NULL,
	`LoggedIn` BIT NOT NULL,
	`OrganisationId` INT NULL,
	`RoleId` INT NOT NULL,
	`Id` INT NOT NULL AUTO_INCREMENT,
	CONSTRAINT PRIMARY KEY(`Id`),
	CONSTRAINT uc_Person_Email UNIQUE (Email),
	CONSTRAINT fk_PersonOrganisationId FOREIGN KEY (`OrganisationId`) REFERENCES `Organisation` (`id`),
	CONSTRAINT fk_PersonRoleId FOREIGN KEY (`RoleId`) REFERENCES `Role` (`id`));

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql: CREATE TABLE Role
-- Created on Tuesday 27th of April 2021 09:54:22 AM
-- 
DROP TABLE IF EXISTS `Role`;
CREATE TABLE `Role` (
	`Short` VARCHAR (50) NOT NULL,
	`Code` VARCHAR (50) NOT NULL,
	`Name` NVARCHAR (255) NOT NULL,
	`UpdatedOn` DATETIME NOT NULL,
	`Id` INT NOT NULL AUTO_INCREMENT,
	CONSTRAINT PRIMARY KEY(`Id`),
	CONSTRAINT uc_Role_Short UNIQUE (Short),
	CONSTRAINT uc_Role_Code UNIQUE (Code));

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql: CREATE TABLE Procedure
-- Created on Tuesday 27th of April 2021 09:54:22 AM
-- 
DROP TABLE IF EXISTS `Procedure`;
CREATE TABLE `Procedure` (
	`Code` VARCHAR (50) NOT NULL,
	`Naam` NVARCHAR (255) NOT NULL,
	`Heading` NVARCHAR (255) NOT NULL,
	`Description` NVARCHAR (255) NULL,
	`UpdatedOn` DATETIME NOT NULL,
	`Id` INT NOT NULL AUTO_INCREMENT,
	CONSTRAINT PRIMARY KEY(`Id`),
	CONSTRAINT uc_Procedure_Code UNIQUE (Code),
	CONSTRAINT uc_Procedure_Naam UNIQUE (Naam));

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql: CREATE TABLE RoleProcedure
-- Created on Tuesday 27th of April 2021 09:54:22 AM
-- 
DROP TABLE IF EXISTS `RoleProcedure`;
CREATE TABLE `RoleProcedure` (
	`Code` VARCHAR (50) NOT NULL,
	`RoleId` INT NOT NULL,
	`ProcedureId` INT NOT NULL,
	`Id` INT NOT NULL AUTO_INCREMENT,
	CONSTRAINT PRIMARY KEY(`Id`),
	CONSTRAINT uc_RoleProcedure_Code UNIQUE (Code),
	CONSTRAINT fk_RoleProcedureRoleId FOREIGN KEY (`RoleId`) REFERENCES `Role` (`id`),
	CONSTRAINT fk_RoleProcedureProcedureId FOREIGN KEY (`ProcedureId`) REFERENCES `Proecedure` (`id`));

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql: CREATE TABLE Step
-- Created on Tuesday 27th of April 2021 09:54:22 AM
-- 
DROP TABLE IF EXISTS `Step`;
CREATE TABLE `Step` (
	`Name` NVARCHAR (255) NOT NULL,
	`ActionId` INT NOT NULL,
	`Description` NVARCHAR (255) NULL,
	`RoleProcedureId` INT NOT NULL,
	`Order` INT NULL,
	`Data` NVARCHAR (4096) NOT NULL,
	`Id` INT NOT NULL AUTO_INCREMENT,
	CONSTRAINT PRIMARY KEY(`Id`),
	CONSTRAINT fk_StepActionId FOREIGN KEY (`ActionId`) REFERENCES `Action` (`Id`),
	CONSTRAINT fk_StepRoleProcedureId FOREIGN KEY (`RoleProcedureId`) REFERENCES `RoleProcedure` (`Id`));

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql: CREATE TABLE Action
-- Created on Tuesday 27th of April 2021 09:54:22 AM
-- 
DROP TABLE IF EXISTS `Action`;
CREATE TABLE `Action` (
	`Code` NVARCHAR (10) NOT NULL,
	`Name` NVARCHAR (255) NOT NULL,
	`Data` NVARCHAR (255) NULL,
	`Id` INT NOT NULL AUTO_INCREMENT,
	CONSTRAINT PRIMARY KEY(`Id`),
	CONSTRAINT uc_Action_Code UNIQUE (Code));

-- With the MySQL FOREIGN_KEY_CHECKS variable,
-- you don't have to worry about the order of your
-- DROP and CREATE TABLE statements at all, and you can
-- write them in any order you like, even the exact opposite.
SET FOREIGN_KEY_CHECKS = 1;