Database Structure

A detail description of our MariaDB/MYSQL database structure. (It's not the best but it's honest work 👀)

Updated for V2.0

Tables

• plotsystem_ftp_configurations
• plotsystem_countries
• plotsystem_servers
• plotsystem_city_projects
• plotsystem_builders
• plotsystem_reviews
• plotsystem_difficulties
• plotsystem_plots

plotsystem_ftp_configurations

Configuration data for the ftp servers.

• schematic_path specifies the path to the schematic folder. If no path is specified, /var/lib/Plot System/schematics is used by default.
• address, port, username & password credentials for the fpt server.

Example:

schematic_path:  "/plot-system/schematics"
address:         "177.013"
port:            22
username:        "jo_kil"
password:        "********"

plotsystem_countries

All countries within one Build team are stored in this table.

• server_id refers to the server the country runs on.
• name is the country ISO code.
• head_id is the id for the playerhead corresponding to the country flag. Player head ids can be found at https://minecraft-heads.com/. If no id is specified, a steve head will be used by default.

Example:

server_id:  2
name:       "AT"
head_id:    "40404"

plotsystem_servers

This table is for all Terra servers.

• ftp_configuration_id the fpt server from which to paste from. Only leave blank if you use simbolic links. An ftp connection should only be used by one server, as this may otherwise lead to unexpected behaviour (pasting twice or inconsitently pasting to one server).
• name name of the server.

Example:

ftp_configuration_id:  1
name:                  "nitrado-net-ftp"

plotsystem_city_projects

The city projects each contain a number of plots. Multiple buildsites can be present in a singe city.

• country_id what country the city is in.
• name build project name.
• description build site description.
• visible if the buildsite is shown to builders / can be build on.

Example:

country_id:   1
name:         "vienna city center"
description:  "the center of the city of vienna"
visible:      1

plotsystem_builders

Table for builders, which can build on plots.

• uuid builder uuid.
• name builder name.
• scoretotal accumulated number of points.
• completed_plots number of plots completed.
• first_slot, second_slot & third_slot in each of these attributes one plot is store, this is to ensure that only 3 plots can be built at once. This value is NULL if the plot has no slot.

Example:

uuid:             "c36ceb6f-da46-43de-a49a-7137cd5d5552"
name:             "JO_KIL"
score:            153
completed_plots:  37
first_slot:       345
second_slot:      NULL
third_slot:       457

plotsystem_reviews

Reviews for a plot, multiple reviews can exist for one plot if a build is rejected.

• reviewer_uuid the uuid of the player reviewing the plot
• rating the rating of each category in a stringified array. Each category has a value from 0-5 (0-> plot is rejected, 1 -> worst, 5 -> best). There are 4 categories (accuracy, block pallete, detailing, technique).
• feedback the written feedback of the plot
• review_date timestamp when review is completed
• sent boolean if the builder of the plot has recieved his review. This happens automatically when the builder joins the server.

Example:

reviewer_uuid:  "c36ceb6f-da46-43de-a49a-7137cd5d5552"
rating:         "2,4,5,3"
feedback:       "nice plot (‾◡◝)"
review_date:    2001-9-11
sent:           1

plotsystem_difficulties

Different difficulty tiers for the plots. Builders can only build on a certain difficulty if thier score is high enough.

• name name of the difficulty.
• multiplier score multiplier for this plot. The score is calculated by multiplying the review sum with this value.
• score_requirement the minumum score a builder requires to build this plot

Example:

name:               "Literally Impossible"
multiplier:         100000
score_requirement:  1000

plotsystem_plots

• city_project_id id of the city the plot is in.
• difficulty_id id of the plot difficulty.
• review_id id of the plots review. Is NULL if no review has been submitted for this plot.
• owner_uuid uuid of the plot owner. Is NULL if building on this plot has not started.
• member_uuids uuids of invited players. Stored as stringified array. Max ammount of invited players is 4. Is NULL if no players have been invited.
• status the completion status of the plot.
• mc_coordinates xyz-coordinates of the plot in the Terra-server. Stored as stringified array.
• score the total score of this plot. This score is calculated by multiplying the sum of the review categories with the difficulty multiplier. The total score is then distributed evenly among all builders in this plot. Is NULL if the plot it not reviewed.
• last_activity when the player last teleported to this plot. If this date is older than 14 days, the plot is automatically reset. Is NULL if the plot is not claimed or nobody has ever teleported to it.
• create_date date when the plot has been created.
• create_player uuid of the player who created this plot. Only architects are able to do this.
• pasted if the plot has been pasted to the Terra 1:1 server.

Example:

city_project_id:  3
difficulty_id:    2
review_id:        345
owner_uuid:       "c36ceb6f-da46-43de-a49a-7137cd5d5552"
member_uuids:     "0763064d-9763-423f-852a-e0d600aa7ddf,d2b93028-a86f-4030-8f11-a3b691618306"
status:           "completed"
mc_coordinates:   "64567,-1564,1456753"
score:            40
last_activity:    2021-11-14
create_date:      1985-07-18
create_player:    "3db5b0e7-389c-485f-9d7a-42573a4ebd80"
pasted:           1