This is a pretty broad question, but I’ll do my best to help you nail (or at least point) in the right direction.
Personally, I’d rather use MongoDB – you could use MySQL or a similar DBMS, but since we’re essentially storing “clans” (aka groups), it makes sense to store the members as JSON objects.
Next, I’d structure the clan json similar to this:
[
{
"clanID": 1,
"clanName":"Group 1",
"clanOwner":"Capt_George",
"clanMembers":[
{
"name":"JohnDoe",
"rank":"OptionalField"
},
{
"name":"JaneDoe",
"rank":"OptionalField"
}
]
}
]
I also included a rank KVP for if you wish to include any other info/stats in the future (e.g. assigning a “class” to users in the clan, just for the heck of it).
So once you’ve planned that out, you’re going to setup a few API endpoints on your own server:
POST /api/createClan {clanName: "DevClan", clanOwner: "Capt_George", key: "SecretPrivateKey"}
This API endpoint should create a JSON document in the MongoDB collection (as structured to the one I posted earlier)
POST /api/joinClan {clanID: 1, name: "JohnDoe", key: "SecretPrivateKey"}
This API should append the user inside of the clanMembers object inside of the MongoDB document. (I suggested passing name, but you can pass anything you want; name, ID, etc…)
POST /api/leaveClan {clanID: 1, name: "JohnDoe", key: "SecretPrivateKey"}
This should remove the KVP from the clanMembers object where the name == the name that was passed through the POST request. Plus, this can also be used for kicking() users (since in a nutshell they both remove users, so no reason to add more API endpoints than necessary)
GET /api/clanInfo {clanID: 1, key: "SecretPrivateKey"}
This should return the JSON document for said requested clan, e.g. this will return the members, info, etc…
SecretPrivateKey should be similar to that of a private API key. No one should know it, this is used to verify the requests are coming from a legitimate source and not faked from a user.
You can use whatever you language you want for the web-server, though personally I’d suggest using Express (NodeJS), and if you do decide to use it you can message me anytime (most comfortable with that). You can also use PHP, but I couldn’t guarantee I’d be able to help (been a few years since I’ve used PHP).
Also with this system, there is no need to continually pull the database. Simply re-call the APIs and update all the in-game elements after a change occurs (aka a POST request is made – (data is changed) ).
Apologies for the lack of organization, wrote this at 2:35 AM so not in the best state of mind
If you have any questions, do let me know and I want to know how this works out!