Mass Storage w/ MySQL

Discussion in 'Spigot Plugin Development' started by MrWaffleman, May 14, 2016.

  1. How would one store mass storage? This is more of a "future reference" question. In MySQL there are rows and tables, but what if that isn't enough? Like for instance, storing friends. It would be pretty annoying to parse/serialize very long string, including editing them whenever someone unfriends them. Should you create a table like PlayerUUID_friends? Would that overload the database with tables? I would like some feedback ^.^.
     
  2. Depends on what you mean by mass storage. Can you elaborate a bit on that?

    If I were you, I'd use an intermediate table between your table Player and Friends, but that might be a bit complex if you know little about MySQL.
     
  3. Before when I used YAML files, I just needed to do something like so:

    Code (Text):
    PlayerWithFriends:
    - guy1
    - guy2
    - guy3
    But in MySQL, it's Column and Row based, and let's say there was a table that stored information about players. (I know this is not the format, but you know the jist)

    Player1:
    - Coins: 22131
    - Kills: 123
    - Deaths 321
    - Friends: ??? (how can I put in multiple friends when it's accustomed for only 1 input, not a whole string, or a serialized string)
     
  4. RiotShielder

    Supporter

    The easiest option would just be to store the UUIDs in a comma separated list, however that'd become a very, very long string after they make a few friends. MySQL's TEXT datatype supports ~65000 characters, at 33 characters per UUID (32 plus a comma), each player can have about 2000 friends.
     
    • Useful Useful x 1
  5. This is a more complex aspect of relation databases such as MySQL. To be able to have a 1 to *, you need to do something like this:

    PlayerTable
    - UUID
    - MoreInfoAboutPlayer

    FriendsTable
    - FriendUUID
    - PlayerUUID

    To get all the friends from a certain player from PlayerTable, your query would look something like this:
    Code (Text):
    SELECT *
    FROM PlayerTable p
    INNER JOIN FriendsTable f
    ON p.UUID = f.PlayerUUID
     
    • Like Like x 1
  6. Serializator

    Supporter

    To elaborate on what @Ferdz said, instead of storing both UUIDs of the friends you can store a reference to the `players` table by putting in the id of the row from the player in the `players` table, also known as a foreign key.

    I recommend you learn more about MySQL before starting to work on this kind of system.
     
    • Informative Informative x 1
  7. Yeah I was trying to make it simple. But of course it would be more efficient to have an identity on PlayerTable and have FriendsTable reference it back