Pour l’application de gestion et de partage de séances sportives sur laquelle je travaille, et que j’ai relancé içi , je me dois de structurer les données de manière cohérente. Et ça pour un novice comme moi, c’est loin d’être évident.
Chaque question apporte de nouvelles questions et pas mal remises en question des décisions prises à l’étape précédente. Pour mettre un terme à cette boucle, je suis reparti de la base et j’ai essayé de construire étape par étape ce que j’espère sera un modèle de données efficace.
Pour décrire les données et la manière dont j’ai choisi de les stocker, je vais utiliser 2 formes différentes : une première schématique et une deuxième sous forme de code SQL (pour la création des tables).
L’Utilisateur, son Profil et ses Amis
Commençons par les données nécessaires à l’authentification d’un utilisateur.
- Un Utilisateur doit avoir un email et un mot de passe.
- Il a un Profil Utilisateur.
- Il peut être ami avec un ou plusieurs Utilisateur.
- Cette relation doit être réciproque. si U1 est ami de U2, alors U2 est forcément ami de U1.
Cet utilisateur a un profil dans lequel est stocké tout ce qui le concerne.
- Un Profil Utilisateur doit avoir un nom, un genre, une année de naissance, un poids et une taille.
- Il peut avoir un avatar, descriptif, une biographie et une position.
- il appartient à un seul Utilisateur.
La relation entre l’Utilisateur et le Profil Utilisateur est assez simple et correspond à une relation One-To-One.
La relation Amis entre Utilisateurs est une relation Many-To-Many. Elle nécessite la création d’une table Amis dans laquelle sera listée toutes les relations entre Utilisateurs.
On peut représenter le stockage de ces données sous la forme ci-dessous :
On peut transcrire en language SQL comme suit :
-- -----------------------------------------------------
-- Table `mydb`.`Users`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`Users` (
`id` VARCHAR(255) NOT NULL,
`email` VARCHAR(255) NOT NULL,
`password` VARCHAR(255) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE INDEX `ux_users_id` (`id` ASC) VISIBLE)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `mydb`.`UsersProfiles`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`UsersProfiles` (
`id` VARCHAR(255) NOT NULL,
`name` TINYTEXT NOT NULL,
`gender` TINYTEXT NOT NULL,
`height` INT NOT NULL,
`weight` DECIMAL(3,2) NOT NULL,
`avatar` MEDIUMTEXT NULL,
`biography` LONGTEXT NULL,
`location` MEDIUMTEXT NULL,
`user` VARCHAR(255) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE INDEX `ux_usersprofiles_id` (`id` ASC) VISIBLE)
CONSTRAINT `fk_user` FOREIGN KEY (`user`) REFERENCES `mydb`.`Users` (`id`),
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `mydb`.`Friends`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`Friends` (
`user` VARCHAR(255) NOT NULL,
`friend` VARCHAR(255) NOT NULL,
PRIMARY KEY (`user`, `friend`),
INDEX `ix_friends_user` (`user` ASC) VISIBLE,
INDEX `ix_friends_friend` (`friend` ASC) VISIBLE,
CONSTRAINT `fk_user` FOREIGN KEY (`user`) REFERENCES `mydb`.`Users` (`id`),
CONSTRAINT `fk_friend` FOREIGN KEY (`friend`) REFERENCES `mydb`.`Users` (`id`)
)
ENGINE = InnoDB;
Les Activitiés, les Séances et les Records
Les Activités vont souvent être utilisées. Il parait donc logique de leur dédier une table.
- Une Activité doit avoir un nom, une icône et indiquer si elle utilise de la distance.
Ensuite il y a bien sûr la Séance, ou session, sportive.
- Une Séance doit avoir un nom, une date et heure de départ et un nombre total de calories brûlées.
- Elle peut avoir une distance, une vitesse moyenne, une trace GPS et une description.
- Elle a été pratiquée dans une seule Activité.
- Elle a été réalisée par un seul Utilisateur.
- Elle peut avoir battu un ou plusieurs Records.
Les interactions entre Utilisateurs, Séances et Activités peuvent être décrites ainsi :
Le code SQL pour la création des tables Activités et Sessions pourra ressembler à ça :
-- -----------------------------------------------------
-- Table `mydb`.`Activities`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`Activities` (
`id` VARCHAR(255) NOT NULL,
`icon` BLOB NOT NULL,
`name` TINYTEXT NOT NULL,
`use_distance` BOOLEAN NOT NULL,
PRIMARY KEY (`id`),
UNIQUE INDEX `ux_activities_id` (`id` ASC) VISIBLE
)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `mydb`.`Sessions`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`Sessions` (
`id` VARCHAR(255) NOT NULL,
`name` TINYTEXT NOT NULL,
`start_date_time` DATETIME NOT NULL,
`duration` INT NOT NULL,
`burned_calories` INT NOT NULL,
`distance` INT NULL,
`average_speed` FLOAT NULL,
`description` MEDIUMTEXT NULL,
`gps_track` JSON NULL,
`user` VARCHAR(255) NOT NULL,
`activity` VARCHAR(255) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE INDEX `ux_sessions_id` (`id` ASC) VISIBLE,
INDEX `ix_sessions_user` (`user_id` ASC) VISIBLE,
INDEX `ix_sessions_activity` (`activity_id` ASC) VISIBLE,
CONSTRAINT `fk_user` FOREIGN KEY (`user`) REFERENCES `mydb`.`Users` (`id`),
CONSTRAINT `fk_activity` FOREIGN KEY (`activity`) REFERENCES `mydb`.`Activities` (`id`)
)
ENGINE = InnoDB;
La gestion des Records est une première difficulté. En effet, un record existe que s’il a été établi lors d’une séance sportive par un utilisateur. Un record est toujours lié à une activité sportive et une distance. Il serait intéressant que l’historique des records soit conservé. Pour cela, j’ai choisi de créer une table qui contiendra les catégories de record, et une autre table qui contiendra les actuels records réalisés par les utilisateurs.
- Une Catégorie de record doit avoir une nom, une description, une distance.
- Elle est liée à une seule Activité.
- Un Record doit avoir un nom, une valeur de record (durée).
- il est lié à une seule Catégorie de record.
- Il peut avoir une description.
- Il doit connaître la Séance qui a inscrit la valeur de record.
On peut représenter ces tables et leurs relations comme suit :
Le code SQL pour la création des tables liées aux Records ressemblera à ça :
-- -----------------------------------------------------
-- Table `mydb`.`RecordTypes`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`RecordTypes` (
`id` VARCHAR(255) NOT NULL,
`name` VARCHAR(255) NOT NULL,
`description` VARCHAR(255) NOT NULL,
`distance` INT NOT NULL,
`activity` VARCHAR(255) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE INDEX `ux_recordtypes_id` (`id` ASC) VISIBLE,
INDEX `ix_recordtypes_activity` (`activity` ASC) VISIBLE,
CONSTRAINT `fk_activity` FOREIGN KEY (`activity`) REFERENCES `mydb`.`Activities` (`id`)
)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `mydb`.`Records`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`Records` (
`id` VARCHAR(255) NOT NULL,
`record_value` FLOAT NOT NULL,
`name` TINYTEXT NOT NULL,
`description` LONGTEXT NULL,
`session` VARCHAR(255) NOT NULL,
`record_type` VARCHAR(255) NOT NULL,
`user` VARCHAR(255) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE INDEX `ux_records_id` (`id` ASC) VISIBLE,
INDEX `ix_records_record_type` (`record_type` ASC) VISIBLE,
INDEX `ix_records_session` (`session` ASC) VISIBLE,
INDEX `ix_records_user` (`user` ASC) VISIBLE,
CONSTRAINT `fk_record_type` FOREIGN KEY (`record_type`) REFERENCES `mydb`.`RecordTypes` (`id`),
CONSTRAINT `fk_session` FOREIGN KEY (`session`) REFERENCES `mydb`.`Sessions` (`id`),
CONSTRAINT `fk_user` FOREIGN KEY (`user`) REFERENCES `mydb`.`Users` (`id`)
)
ENGINE = InnoDB;
Les Objectfis et les Défis
Un objectif est fixé par un utilisateur pour lui-même. Un objectif est atteint par une ou plusieurs séances sportives.
- Un Objectif doit avoir un nom, une date et heure de début, une date et heure de fin, une valeur d’objectif, un statut (pas commencé, en cours, terminé).
- Il ne concerne qu’une seule Activité.
- Il appartient à un seul Utilisateur.
- Une ou plusieurs Séances peuvent permettre de l’atteindre.
- Une Séance peut concerner plusieurs Objectifs.
Il y a ici une relation Many-To-Many entre les Objectifs et les Séances. Il faut pour réaliser cette relation mettre en place une table d’association qui enregistrera les relations entre Objectifs et Séances. On peut représenter ces tables et leurs relations comme suit :
Le code SQL pour la création des tables liées aux Objectifs ressemblera à ça :
-- -----------------------------------------------------
-- Table `mydb`.`Objectives`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`Objectives` (
`id` VARCHAR(255) NOT NULL,
`name` VARCHAR(255) NOT NULL,
`end_date_time` DATETIME NOT NULL,
`start_date_time` DATETIME NOT NULL,
`objective_value` INT NOT NULL,
`user` VARCHAR(255) NOT NULL,
`activity` VARCHAR(255) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE INDEX `ux_objectives_id` (`id` ASC) VISIBLE,
INDEX `ix_objectives_user` (`user` ASC) VISIBLE,
INDEX `ix_objectives_activity` (`activity` ASC) VISIBLE,
CONSTRAINT `fk_user` FOREIGN KEY (`user`) REFERENCES `mydb`.`Users` (`id`),
CONSTRAINT `fk_activity` FOREIGN KEY (`activity`) REFERENCES `mydb`.`Activities` (`id`)
)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `mydb`.`ObjectivesSessions`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`ObjectivesSessions` (
`objective` VARCHAR(255) NOT NULL,
`session` VARCHAR(255) NOT NULL,
INDEX `ix_objectivessessions_objective` (`objective` ASC) VISIBLE,
INDEX `ix_objectivessessions_session` (`session` ASC) VISIBLE,
CONSTRAINT `fk_objective` FOREIGN KEY (`objective`) REFERENCES `mydb`.`Objectives` (`id`),
CONSTRAINT `fk_session` FOREIGN KEY (`session`) REFERENCES `mydb`.`Sessions` (`id`)
)
ENGINE = InnoDB;
Un défi est une proposition d’objectif d’un utilisateur à ces amis. Il va garder les caractéristiques d’un objectif, mais aura également des relations avec d’autres utilisateurs.
- Un Défi doit avoir un nom, une description, une distance d’objectif, une date et heure de début, une date et heure de fin, un statut (pas commencé, en cours, terminé).
- Il ne concerne qu’une seule Activité.
- Un Utilisateur en est l’unique auteur.
- Il contient une liste d’Utilisateurs participants, qui dispose d’un statut sur ce Défi (pas commencé, en cours, terminé).
- Une ou plusieurs Séances peuvent permettre à un Utilisateur de le terminer.
- Une Séance d’un Utilisateur peut participer à plusieurs Défis.
Le principe utilisé pour les Objectifs est repris pour les Challenges avec une table d’association ChallengesUsers pour répertorier les Utilisateurs qui participent à un Challenge, et une table d’association ChallengesSessions pour lister les Utilisateurs et leurs Séances qui contribuent à ce Challenge.
-- -----------------------------------------------------
-- Table `mydb`.`Challenges`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`Challenges` (
`id` VARCHAR(255) NOT NULL,
`name` TINYTEXT NOT NULL,
`description` LONGTEXT NOT NULL,
`start_date_time` DATETIME NOT NULL,
`end_date_time` DATETIME NOT NULL,
`distance` INT NOT NULL,
`status` TINYTEXT NOT NULL,
`description` LONGTEXT NULL,
`author` VARCHAR(255) NOT NULL,
`activity` VARCHAR(255) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE INDEX `ux_challenges_id` (`id` ASC) VISIBLE,
INDEX `ix_challenges_author` (`author` ASC) VISIBLE,
INDEX `ix_challenges_activity` (`activity` ASC) VISIBLE,
CONSTRAINT `fk_user` FOREIGN KEY (`author`) REFERENCES `mydb`.`Users` (`id`),
CONSTRAINT `fk_activity` FOREIGN KEY (`activity`) REFERENCES `mydb`.`Activities` (`id`)
)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `mydb`.`ChallengesUsers`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`ChallengesUsers` (
`challenge` VARCHAR(255) NOT NULL,
`participant` VARCHAR(255) NOT NULL,
`participant_status` TINYTEXT NOT NULL,
PRIMARY KEY (`challenge`, `user`, `participant`),
INDEX `ix_challengesusers_challenge` (`challenge` ASC) VISIBLE,
INDEX `ix_challengesusers_participant` (`user` ASC) VISIBLE,
CONSTRAINT `fk_challenge` FOREIGN KEY (`challenge`) REFERENCES `mydb`.`Challenges` (`id`),
CONSTRAINT `fk_user` FOREIGN KEY (`participant`) REFERENCES `mydb`.`Users` (`id`)
)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `mydb`.`ChallengesSessions`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`ChallengesSessions` (
`challenge` VARCHAR(255) NOT NULL,
`session` VARCHAR(255) NOT NULL,
`participant` VARCHAR(255) NOT NULL,
PRIMARY KEY (`challenge`, `session`, `participant`),
INDEX `ix_challengessessions_challenge` (`challenge` ASC) VISIBLE,
INDEX `ix_challengessessions_session` (`session` ASC) VISIBLE,
INDEX `ix_challengessessions_participant` (`participant` ASC) VISIBLE,
CONSTRAINT `fk_challenge` FOREIGN KEY (`challenge`) REFERENCES `mydb`.`Challenges` (`id`),
CONSTRAINT `fk_session` FOREIGN KEY (`session`) REFERENCES `mydb`.`Sessions` (`id`),
CONSTRAINT `fk_participant` FOREIGN KEY (`participant`) REFERENCES `mydb`.`Users` (`id`)
)
ENGINE = InnoDB;
Conclusion (ou pas…)
J’ai commencé à rédiger cet article pour mettre de l’ordre dans mes idées autour des données de cette application. Il va certainement continuer à évoluer, surtout quand je vais commencer à utiliser ce qui est décrit plus haut dans du vrai code.