Search code examples
sqlmysqldatabase-designclass-diagram

Transforming a class diagram to a SQL database

I have this part of a class diagram that I want to translate into SQL:

Piece of class diagram:

My internship supervisor sent me this SQL code:


CREATE TABLE `users` (
  `Matricule` varchar(50) NOT NULL,
  `Password` varchar(50) NOT NULL,
  `Role` enum('ResponsableProduction','ResponsableChaineProduction','ResponsableMaintenance','AgentMaintenance','Administrateur','Magasinier') NOT NULL
);

CREATE TABLE `responsable_production` (
  `Matricule` varchar(50) NOT NULL,
  `Nom` varchar(25) NOT NULL,
  `Prenom` varchar(25) NOT NULL
);

CREATE TABLE `responsable_maintenance` (
  `Matricule` varchar(50) NOT NULL,
  `Nom` varchar(25) NOT NULL,
  `Prenom` varchar(25) NOT NULL,
  `RefChaine` varchar(50) NOT NULL
);

CREATE TABLE `responsable_chaine_production` (
  `Matricule` varchar(50) NOT NULL,
  `Nom` varchar(25) NOT NULL,
  `Prenom` varchar(25) NOT NULL,
  `RefChaine` varchar(50) NOT NULL
);

CREATE TABLE `magasinier` (
  `Matricule` varchar(50) NOT NULL,
  `Nom` varchar(25) NOT NULL,
  `Prenom` varchar(25) NOT NULL
);

CREATE TABLE `agent_maintenance` (
  `Matricule` varchar(50) NOT NULL,
  `Nom` varchar(25) NOT NULL,
  `Prenom` varchar(25) NOT NULL,
  `Specialite` varchar(50) NOT NULL,
  `Age` int(11) NOT NULL,
  `Sexe` enum('Homme','Femme','','') NOT NULL,
  `NiveauEducation` varchar(50) NOT NULL,
  `ExperienceProfessionnelle` int(11) NOT NULL
);

-- Triggers `users`
--
DELIMITER $$
CREATE TRIGGER `User_Insert` AFTER INSERT ON `users` FOR EACH ROW BEGIN
    IF (NEW.Role = "ResponsableMaintenance") THEN
        insert into responsable_maintenance(matricule) VALUES (NEW.matricule);
    ELSEIF (NEW.Role = "ResponsableChaineProduction") THEN
        insert into responsable_chaine_production(matricule) VALUES (NEW.matricule);
    ELSEIF (NEW.Role = "AgentMaintenance") THEN
        insert into agent_maintenance(matricule) VALUES (NEW.matricule);
    ELSEIF (NEW.Role = "ResponsableProduction") THEN
        insert into responsable_production(matricule) VALUES (NEW.matricule);
    ELSEIF (NEW.Role = "Magasinier") THEN
        insert into magasinier(matricule) VALUES (NEW.matricule);
    End IF;
END
$$
DELIMITER ;

But it didn't seem quite right to me. Because, for example if I'm adding a new user, first I have to add it to the user table, then add his remaining details in one of the other table depending on his role. That seemed a lot of trouble to me, and inconsistency problems might arise later on. Is this SQL script consistent with the Class Diagram? (after adding the needed PK and FK constraints into it ofc). And if yes, is there a simpler/safer database design than the one my supervisor sent me?

Solution

  • The challenge in implementing your UML diagram into SQL tables, is the inheritance. Inheritance is not a concept in SQL, and several techniques exist to map such an UML model to a relational model:

    • single table inheritance: you'd have one table for the role that would would contain (nullable) columns for all possible attribute in any roles, as well as a code that helps to know which kind of role the row is about:

      CREATE TABLE `role` (
  `IdRole` varchar(10) NOT NULL,
  `NomRole` varchar(50) NOT NULL,
  `TypeRole` enum ('ResponsableProduction','ResponsableChaineProduction', 'ResponsableMaintenance','AgentMaintenance','Administrateur','Magasinier') NOT NULL
  `Specialite` varchar(50)     -- Used only if maintenance agent, NULL otherwise
);

    • class table inheritance: you'd have a table for each class in the inheritance hierarchy, and relationships of each class with its parent/child classes. The can be very cumbersome if many classes are empty or have not many differences.

    • concerete table inheritance: you'd have a table for each concrete class but none for abstract parents. Each table has all the necessary rows: the ones inherited, and its own.

    Your supervisor seems to play with you: he/she used concrete table inheritance, because you have all the role specialisations and not the abstract role. Unfortunately, it uses rows which are related to user, which is dramatically false. Moreover with the multiple roles possible, it would mean to duplicate information if a user has several roles.

    Once you have sorted out, what really belongs to the role tables, and what belongs to the user table, you ara ready to create the n to n mapping table, between users and roles.