Database Schema

The backend API of CorTest uses a MySQL database to store patient information.
This page describes the structure of the main table used: utilisateurs.

---

Table: utilisateurs

This table stores all information needed to identify a patient and associate them with their testing data.

create_table_utilisateurs.sql

CREATE TABLE utilisateurs (
    id VARCHAR(128) NOT NULL PRIMARY KEY,
    nom VARCHAR(50) DEFAULT NULL,
    prenom VARCHAR(50) DEFAULT NULL,
    date_naissance VARCHAR(150) DEFAULT NULL,
    adresse TEXT DEFAULT NULL,
    neurologue VARCHAR(100) DEFAULT NULL,
    mot_code VARCHAR(255) DEFAULT NULL
);

Field Description

Field	Type	Description
id	VARCHAR(128)	Primary key. Corresponds to the Firebase UID of the user.
nom	VARCHAR(50)	Patient's last name
prenom	VARCHAR(50)	Patient's first name
date_naissance	VARCHAR(150)	Date of birth (can be stored as string for flexibility)
adresse	TEXT	Postal address
neurologue	VARCHAR(100)	Name of the neurologist assigned to the patient
mot_code	VARCHAR(255)	Keyword used during memory-related tests (entered by patient or doctor)

Firebase UID

The id field is directly linked to Firebase authentication. Each registered user receives a unique UID from Firebase, which is stored in this column.

Learn more about Firebase Authentication

Notes

• The mot_code field is essential for memory recall exercises and must be securely handled.
• date_naissance is stored as a string for simplicity, but could be converted to DATE if date operations become necessary.
• Currently, no foreign keys or relationships are defined. This schema assumes a simple setup focused on user registration.

Related Routes

The following API endpoints interact with this table:

• POST /register → Insert new user into the database.
• GET /profil/:uid → Retrieve user info using Firebase UID.

See the API Routes documentation for implementation details.

---

Example Entry

Example utilisateur object

{
  "id": "Gdu72TfKsAjr6cLn7oYdSzwF3yF3",
  "nom": "Durand",
  "prenom": "Claire",
  "date_naissance": "1984-05-17",
  "adresse": "12 rue du Général de Gaulle, 67000 Strasbourg",
  "neurologue": "Dr. Lemoine",
  "mot_code": "mimosa"
}

Future improvements

We will link the deletion of a user in Firebase with the deletion of their entry in this table.