SQL structure to save coordinates for spigot plugin

Question

I'm writing a spigot plugin where I give the user the possibility to send a message to the admins of the server. 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.

I have got a simple sql table, which stores the message, the player's uuid, etc in a table, but I really dislike the idea that I have to store all three values of the Vec3 in separate columns. I googled for a possibility to simply store a Vec3 via SQL, but had no luck.

Here's an overview of my current table:

I just have this one table with the data in it, which I create like this:

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));

I'd like to know how I can improve in this matter, or the general way how my data is stored. I'm particularly interested in alternative methods to store these coordinates, I don't know why, I find my current way very unsatisfying.

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.