Employees MySQL Database

dump.sql

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET AUTOCOMMIT = 0;
START TRANSACTION;
SET time_zone = "+00:00";

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

CREATE DATABASE IF NOT EXISTS `database-structure` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
USE `database-structure`;

CREATE TABLE `employees` (
  `ID` int(11) NOT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  `name` varchar(255) NOT NULL,
  `birthday` date NOT NULL,
  `ssn_code` varchar(255) NOT NULL,
  `status` tinyint(1) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `employees` (`ID`, `created_at`, `updated_at`, `name`, `birthday`, `ssn_code`, `status`) VALUES
(1, '2019-12-15 00:00:00', '2019-12-15 00:00:00', 'Elizabeth S. Bevers', '1991-12-02', '30668051', 1),
(2, '2019-12-15 00:00:00', '2019-12-15 00:00:00', '72890410Helen D. Dunn', '1987-08-04', '72890410', 1),
(3, '2019-12-15 00:00:00', '2019-12-15 00:00:00', 'Robert V. Costello\r\n', '1994-02-03', '29986112', 1);

CREATE TABLE `employees_contact_info` (
  `ID` int(11) NOT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  `employee_id` int(11) NOT NULL,
  `email` varchar(255) NOT NULL,
  `phone` varchar(128) NOT NULL,
  `address` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `employees_contact_info` (`ID`, `created_at`, `updated_at`, `employee_id`, `email`, `phone`, `address`) VALUES
(1, '2019-12-15 00:00:00', '2019-12-15 00:00:00', 3, 'RobertVCostello@armyspy.com', '443-605-8865', '2274 Hewes Avenue, Baltimore, MD 21201'),
(2, '2019-12-15 00:00:00', '2019-12-15 00:00:00', 2, 'HughAMusson@rhyta.com', '301-974-0336', '587 Lake Floyd Circle, Lanham, MD 20706'),
(3, '2019-12-15 00:00:00', '2019-12-15 00:00:00', 1, 'DeannaJCasillas@jourrapide.com', '310-977-3777', '2441 Prospect Valley Road, Gardena, CA 90248');

CREATE TABLE `employees_experience` (
  `ID` int(11) NOT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  `employee_id` int(11) NOT NULL,
  `introduction` text NOT NULL,
  `experience` text NOT NULL,
  `education` text NOT NULL,
  `language` enum('english','spanish','french') NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `employees_experience` (`ID`, `created_at`, `updated_at`, `employee_id`, `introduction`, `experience`, `education`, `language`) VALUES
(4, '2019-12-15 00:00:00', '2019-12-15 00:00:00', 1, 'Confident and articulate individual with commended ability to work with others to achieve set outcomes. Young yet mature school leaver with developed employability skills and personal attributes that support the transition from education to full-time employment. Leadership and communication skills developed and demonstrated through engagement in sports clubs and associations. Motivated individual keen to obtain employment in warehouse and operations environment. Key strengths include strong attention to detail and willingness to learn and develop professionally.', 'Pizza Hut, Newark, NJ\r\n\r\n2005–2009\r\n\r\nWorked passionately in customer service in a high-volume restaurant.\r\nCompleted the F.A.S.T. customer service training class.\r\nMaintained a high tip average thanks to consistent customer satisfaction.', '2012: Principal’s award for dedication demonstrated by sporting contributions and achievements.\r\n2011: Community recognition award for working as a buddy for newly enrolled Year 7 students.', 'english'),
(5, '2019-12-15 00:00:00', '2019-12-15 00:00:00', 1, 'Individuo confiado y articulado con la habilidad recomendada de trabajar con otros para lograr resultados establecidos. Jóvenes egresados de la escuela con habilidades de empleabilidad desarrolladas y atributos personales que apoyan la transición de la educación al empleo a tiempo completo. Habilidades de liderazgo y comunicación desarrolladas y demostradas a través de la participación en clubes deportivos y asociaciones. Individuo motivado interesado en obtener empleo en el entorno de almacén y operaciones. Las fortalezas clave incluyen una gran atención al detalle y la voluntad de aprender y desarrollarse profesionalmente.', 'Pizza Hut, Newark, Nueva Jersey\r\n\r\n2005-2009\r\n\r\nTrabajó apasionadamente en el servicio al cliente en un restaurante de gran volumen.\r\nCompletado el F.A.S.T. clase de entrenamiento de servicio al cliente.\r\nMantuvo un alto promedio de propinas gracias a la constante satisfacción del cliente.', '2012: Premio del director por dedicación demostrado por contribuciones y logros deportivos.\r\n2011: Premio de reconocimiento comunitario por trabajar como amigo para los estudiantes recién matriculados de Year 7.', 'spanish'),
(6, '2019-12-15 00:00:00', '2019-12-15 00:00:00', 1, 'Personne confiante et articulée avec une capacité louable de travailler avec les autres pour atteindre les résultats fixés. Jeunes sortis de l\'école mais matures avec des compétences développées en employabilité et des attributs personnels qui soutiennent la transition de l\'éducation à l\'emploi à temps plein. Compétences en leadership et en communication développées et démontrées par l\'engagement dans les clubs et associations sportives. Personne motivée désireuse d\'obtenir un emploi dans un entrepôt et un environnement d\'exploitation. Les points forts comprennent une grande attention aux détails et la volonté d\'apprendre et de se développer professionnellement.', 'Pizza Hut, Newark, New Jersey\r\n\r\n2005-2009\r\n\r\nIl a travaillé avec passion au service à la clientèle dans un restaurant à gros volume.\r\nVous avez terminé le F.A.S.T. Cours de formation au service à la clientèle.\r\nIl a maintenu une moyenne élevée de pourboires grâce à la satisfaction constante des clients.', '2012: Prix du directeur pour le dévouement démontré par les contributions et les réalisations sportives.\r\n2011: Prix de reconnaissance communautaire pour travailler en tant que copain pour les étudiants de 7e année nouvellement inscrits.', 'french');

CREATE TABLE `log` (
  `ID` int(11) NOT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  `user_id` int(11) NOT NULL,
  `table_name` varchar(128) NOT NULL,
  `record_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `log` (`ID`, `created_at`, `updated_at`, `user_id`, `table_name`, `record_id`) VALUES
(1, '2019-04-09 00:00:00', '2019-04-09 00:00:00', 1, 'employees', 1);


ALTER TABLE `employees`
  ADD PRIMARY KEY (`ID`);

ALTER TABLE `employees_contact_info`
  ADD PRIMARY KEY (`ID`);

ALTER TABLE `employees_experience`
  ADD PRIMARY KEY (`ID`);

ALTER TABLE `log`
  ADD PRIMARY KEY (`ID`);


ALTER TABLE `employees`
  MODIFY `ID` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;

ALTER TABLE `employees_contact_info`
  MODIFY `ID` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;

ALTER TABLE `employees_experience`
  MODIFY `ID` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=7;

ALTER TABLE `log`
  MODIFY `ID` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;
COMMIT;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

An example query to get 1-person data in all languages

SELECT employees.ID,
       employees.name,
       employees.birthday,
       employees.ssn_code,
       employees.status,
       employees_contact_info.email,
       employees_contact_info.phone,
       employees_contact_info.address,
       employees_experience.introduction,
       employees_experience.education,
       employees_experience.language
FROM employees
JOIN employees_contact_info ON employees.ID = employees_contact_info.employee_id
JOIN employees_experience ON employees.ID = employees_experience.employee_id
WHERE employees.ID = 1