Structure SQL pour enregistrer les coordonnées du plugin spigot

Question

J'écris un plugin spigot où je donne à l'utilisateur la possibilité d'envoyer un message aux administrateurs du serveur. L'une des exigences est que les coordonnées du lecteur soient stockées, lorsque le message est envoyé, et qu'il soit stocké dans une table SQL, afin que les administrateurs sur plusieurs serveurs spigot puissent y accéder indépendamment de l'endroit où le message a été envoyé à l'origine.

J'ai une simple table sql, qui stocke le message, l'uuid du joueur, etc. dans une table, mais je n'aime vraiment pas l'idée que je dois stocker les trois valeurs du Vec3dans des colonnes séparées. J'ai cherché sur Google une possibilité de simplement stocker un Vec3 via SQL, mais je n'ai pas eu de chance.

Voici un aperçu de mon tableau actuel:

J'ai juste cette table avec les données qu'elle contient, que je crée comme ceci:

CREATE TABLE IF NOT EXISTS Meldung (
    id INT AUTO_INCREMENT NOT NULL,
    UUID VARCHAR(64),
    NAME VARCHAR(64),
    MESSAGE TEXT,
    XCOORD INT,
    YCOORD INT,
    ZCOORD INT,
    ISREAD TINYINT(1),
    primary key(id));

J'aimerais savoir comment je peux m'améliorer dans ce domaine, ou comment mes données sont stockées de manière générale. Je suis particulièrement intéressé par les méthodes alternatives pour stocker ces coordonnées, je ne sais pas pourquoi, je trouve ma manière actuelle très insatisfaisante.

Answer 1

One of the requirements is that the coordinates of the player are stored, when the message is sent, and that it is stored in a sql table, so that admins on multiple spigot servers can access them independently of where the message was sent originally.

Which raises questions about transactions and concurrent access.

---

CREATE TABLE IF NOT EXISTS Meldung (
    id INT AUTO_INCREMENT NOT NULL,
    UUID VARCHAR(64),
    NAME VARCHAR(64),
    MESSAGE TEXT,
    XCOORD INT,
    YCOORD INT,
    ZCOORD INT,
    ISREAD TINYINT(1),
    primary key(id));

Your table structure raises many questions. Let's start with the obvious that MySQL table names might or might not be case-sensitive, depending on whether it runs on a filesystem that is case-sensitive or not.

The second is, do you need all columns to be nullable?

Third, I believe that at least UUID and ISREAD should have an index assigned, but that depends on the use-case mostly.

    id INT AUTO_INCREMENT NOT NULL,

Why is the casing different to everything else?

    UUID VARCHAR(64),

That's a bad column name, what UUID? Of what? PLAYER_UUID oder rather PLAYER_ID would be a better choice.

    NAME VARCHAR(64),

Same here, name of what?

    XCOORD INT,
    YCOORD INT,
    ZCOORD INT,

Unless the database provides a fitting datatype, storing the single values of a vector is the best thing you can do. Do not give into the idea "I will store this as string and parse it later", that's going to bite you or someone else down the road.

---

What I'm missing in this table is a CREATED_AT and READ_AT datetime field. The later could even double as flag whether it was read or not, by having it nullable.

What I'd also rather do is have a separate Player table, which allows to not store the name multiple times:

CREATE TABLE Player (
    -- Note that I've skipped a column-name prefix here,
    -- as it is clear what is meant because of the table.
    UUID VARCHAR(64) PRIMARY KEY,
    NAME TEXT NOT NULL
)

CREATE TABLE Meldung (
    -- Primary Keys are not nullable by default.
    ID INT AUTO_INCREMENT PRIMARY KEY,
    PLAYER_UUID VARCHAR(64) FOREIGN KEY REFERENCES Player(UUID),
    MESSAGE TEXT NOT NULL,
    PLAYER_LOCATION_X INT NOT NULL,
    PLAYER_LOCATION_Y INT NOT NULL,
    PLAYER_LOCATION_Z INT NOT NULL,
    -- Could also be SENT_AT.
    CREATED_AT DATETIME NOT NULL DEFAULT NOW(),
    READ_AT DATETIME
)

---

On another note, I like to use lowercase SQL with all columns and table names uppercase, like this:

create table PLAYER (
    -- Note that I've skipped a column-name prefix here,
    -- as it is clear what is meant because of the table.
    UUID varchar(64) primary key,
    NAME text not null
)

create table MELDUNG (
    -- Primary Keys are not nullable by default.
    ID int auto_increment primary key,
    PLAYER_UUID varchar(64) foreign key references PLAYER(UUID),
    MESSAGE text not null,
    PLAYER_LOCATION_X int not null,
    PLAYER_LOCATION_Y int not null,
    PLAYER_LOCATION_Z int not null,
    CREATED_AT datetime not null default NOW(),
    READ_AT datetime
)

As it is quite easier to type, and anything that is upper-case can be easily identified as table or column. It also removes any of the ambiguity regarding case-sensitivity in MySQL.