MySQL Friend System?

Discussion in 'Spigot Plugin Development' started by YogurtSmudge, Sep 14, 2019 at 2:45 AM.

  1. So I'm wondering what's the best way to create a friend system in a MySQL database performance-wise.

    So far, I have seen people use one of two options:

    The first method consists of two columns in the database:

    The first column is obviously the UUID.
    The second column is a long TEXT datatype that separates all of the player's friends with a character (Like a comma)

    UUID Friends
    Player1 Player2,Player3,Player4

    Though, I do have a question: Shouldn't this mean that each individual UUID gets their own row?
    Like this:

    UUID Friends
    Player1 Player2,Player3,Player4
    Player2 Player1,Player3
    Player3 Player1,Player2
    Player4 Player1


    I'm sure this method requires a lot more storage.

    The second method consists of two columns in the database:

    The first column is surprisingly still the UUID.
    The second column is a UUID of a player's friend.

    UUID Friends
    Player1 Player2
    Player1 Player3

    Just like the first method, should the table look like the example above? Or like this:

    UUID Friends
    Player1 Player2
    Player2 Player1
    Player1 Player3
    Player3 Player1

    What's the most efficient way? What are the pros/cons of each method?
     
  2. Tux

    Tux

    The latter, because the database format is far more semantic and is easier to index and query for friend relationships (i.e. does player A have friend B?)
     
    • Agree Agree x 1
  3. Hi! I think you may create method what you want but load all friends in enable process.

    Code (Java):
    private static HashMap<String, ArrayList<String>> friends = new HashMap<>();

    public static HashMap<String, ArrayList<String>> getFriends(){
    return friends;
    }

    public static loadFriends(){
       Connection connection = YouConnectionMethod;
       Statement st = connection.createStatement();
       String SQL = "SELECT * FROM `friends`";
      ResultSet rs = st.executeQuery(SQL);
      // I think good idea create a table like
      // <USERNAME> | <FRIENDS>
      // in table 'friends'.
      // so we mast put into HashMap friends all data.
      // declare ArrayList with friends
      ArrayList<String> friendsofuser = new ArrayList<>();
      while (rs.next){
      String username = rs.getString("username");
      friendofuser = rs.getString("friends").split(" ");
      // that use if you set friends like "Player1 Player2 Player3"
      // if you use "Player1,Player2,Player3" - #split(",");
      friends.put(username, friendofuser)
    }
    }

    And use that into you EnableProcess logick.

    And for get the list friends of user with nick Example we need use where you want "<class_of_getFriends()_method.getFriends().get("Example")
     
  4. There is also a third way.
    You can save every player with an unique id, like "1" called I guess "AUTO_INCREMENT".
    Like that you can get players by that id or save players by that id.
     
  5. And what advantage would that have? Why not use the UUIDs players already have?
     
  6. In bad theory it would save space in the long run, but you'd have to store the UUID as well... because if you have a friend with ID: 2 that doesn't tell much about WHO the player is. To identify them you'd need to store the UUID as well. This way the key would be the ID and the value the UUID... it's less storage to use the UUID as a key and not use an ID.
     
  7. You are storing the uuids, but somewhere you also have to save the friends of a player. At that moment, u can save a single number like 1-9999 instead of a uuid with a length about 16 letters.

    You are right. Otherwise it wouldnt make any sens.
    For example:
    ID; UUID; Friends; Status; OnlineStatus(If you like to work on spigot with your friend system)

    23; RANDOMUUID; 24,19,201,30; &eI love &cChicken; 0(offline)/1(online)

    EDIT:

    Instead of this:

    RANDOMUUID; FRIENDUUID1, FRIENDUUID2, FRIENDUUID3; &eI love &cChicken; 0(offline)/1(online)
     
  8. Strahan

    Benefactor

    Sorry dude, but this is terrible advice. Storing the friends with a non game related numeric index will mean more roundtrips to SQL to get their data. That aside, using a delimited string like this to store data is horribly inefficient and totally makes using an RDBMS pointless. Make a friends table and just store the UUID of the player and UUID of the friend. Then you can easily select a friends list when needed and search much easier.

    PS - and don't store their online status in the friends table. That doesn't belong, and will create a layer of complexity that is totally unnecessary.
     
    #10 Strahan, Sep 14, 2019 at 4:23 PM
    Last edited: Sep 14, 2019 at 4:31 PM
    • Agree Agree x 1
  9. We arent talking about easier, its important to save the datas without wasting memory.
    Its also not hard, if you code an api then you can get the id by uuid and the uuid by id.
     
  10. Strahan

    Benefactor

    *facepalm* You don't throw away the whole point of a relational database to save a few bytes. That's nuts. Making an API is even more silly when you don't have to. If your MySQL server has such limited disk space that you need to do a hacky approach like this, you are in sad straits.
     
    • Agree Agree x 1