6
\$\begingroup\$

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:

enter image description here

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.

\$\endgroup\$
4
  • \$\begingroup\$ Is there a reason why your table name is German, but your column names aren't? \$\endgroup\$
    – forsvarir
    Oct 27, 2020 at 15:18
  • \$\begingroup\$ Yes, the name of the plugin was given as a requirement, and so was the name of the main table. However I dislike naming stuff in other languages than English when related to coding, and decided to keep the rest in English. \$\endgroup\$
    – monamona
    Oct 27, 2020 at 15:21
  • \$\begingroup\$ Is x,y,z sufficient, or do you need something like world id as well? One option would be too have a reporter location table which you join to from the main report table. You are also using int for your x,y,z. Is this correct/sufficient resolution, the definitions I've seen suggest it's usually a floating point number. You could also look at blobs if you really don't like 3columns, but they can be harder to work with. \$\endgroup\$
    – forsvarir
    Oct 27, 2020 at 15:50
  • \$\begingroup\$ No, I use the "block"-Values, for some other reasons, and the block-coord's are always int's \$\endgroup\$
    – monamona
    Oct 28, 2020 at 13:26

1 Answer 1

3
\$\begingroup\$

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.

\$\endgroup\$
1
  • \$\begingroup\$ Lots of useful information here. You actually encouraged me to at last write a custom api for player data storage, so I wrote another plugin just to handle the player data, so multiple plugins can use this PLAYER-Table. Also implemented most of your other suggestions :) \$\endgroup\$
    – monamona
    Oct 28, 2020 at 11:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.