MySQL database best practice

[Vloxan]

I was wondering if it's best practice to query all the information you need on a player when they join and storing it in a Lua table for later access.

I'm wondering what best practice would be when using a MySQL table to store characters. In a row I thought of having SteamID, name, model, and charNum to indicate which character number it is for that player but I am unsure of whether this way is the proper way I should be doing it.

Thanks in advance, hopefully I've provided all the useful information you need.

[lorde banana]

Query what you need when you need it, cache it where you can.

[Vloxan]

[QUOTE=Banana Lord.;52408605]Query what you need when you need it, cache it where you can.[/QUOTE]

Alright this seems to answer my first concern, thanks.

[editline]28th June 2017[/editline]

Come to think of it perhaps the charNum is redundant and name should be a primary key because it's not like it would be wise to allow people to have the same name.

[editline]28th June 2017[/editline]

That being said it's also supposed to be best practice to not have changing primary keys and names could change, names can also be longer than ideal.

[Snowmew]

Admittedly I don't do GMod development but generally speaking you don't want to do multiple queries on a single record, but you don't want to query records unnecessarily. So you don't want to be performing multiple queries on a single character down the line, but you also don't want to return character records that are not relevant (e.g. pulling the whole table and parsing it in Lua to find the character you want) unless you expect to be using that data.

[Vloxan]

I think I've decided to have the SteamID with _ and the character number so for example if I have 3 characters it's going to be 76561198077960145_01, 76561198077960145_02, 76561198077960145_03

I should then be able to return all of these characters for a player when they join like so:

SELECT * FROM characters WHERE id LIKE '76561198077960145%';

[editline]28th June 2017[/editline]

Oops looks like what I'm doing isn't too smart given the ratings (auto merge removes ratings, didn't know that)

[suXin]

Keep data atomic as possible. Either have a table of steam_ids and join characters to them, or if you really want to keep it in single table - keep character index as [B]integer column[/B]

You'll be able to order characters by index and pick a certain character easily.

[Vloxan]

[QUOTE=suXin;52409810]Keep data atomic as possible. Either have a table of steam_ids and join characters to them, or if you really want to keep it in single table - keep character index as [B]integer column[/B]

You'll be able to order characters by index and pick a certain character easily.[/QUOTE]

In terms of SteamID should that also be an index?

[suXin]

[QUOTE=Vloxan;52409830]In terms of SteamID should that also be an index?[/QUOTE]

Not sure what you mean exactly. If you plan to use it as primary key, you will have to make a complex primary key which consists both of SteamID and character index, otherwise you won't be able to keep several characters for one SteamID.

If you mean adding database index for SteamID column - it's not really worth it unless you've got hundreds of thousands SteamIDs that you have to retrieve often. Database indexes are for faster data retrieval at cost of more disk space. Primary key already implies an index though.

[sp]by the way I'm open minded to use SteamIDs as primary key, but in 99% cases I will resort to auto incremented ids for a primary key[/sp]

[Vloxan]

Ok so here's where I am now. I've set the SteamID as a INDEX so I can have multiple rows with the same SteamID and when querying I get back the row for every character, I don't see why I need to store what character number it is anymore.

In my table I have (removed some syntax for the sake of this post):

steamid BIGINT(20), UNSIGNED name varchar(30), model varchar(100)
ALTER TABLE characters ADD INDEX (steamid)

[Revenge282]

List all of the different data you would like to store (SteamIDs, character properties, character inventory properties, etc) and I will help you design a proper MySQL table for it.

[Vloxan]

[QUOTE=Revenge282;52409949]List all of the different data you would like to store (SteamIDs, character properties, character inventory properties, etc) and I will help you design a proper MySQL table for it.[/QUOTE]

Alright so the character table:
- SteamID (to identify the player who owns that character)
- Model (to know which model that character has)
- Character name
- Note: I want one SteamID i.e one account to be able to have multiple characters

Inventory:
- SteamID (to identify what characters inventory it is)
- Item name (or some other ID)
- Item stack / amount

Let me know if you need more info

[SFAriel]

There's also [img]http://wiki.garrysmod.com/favicon.ico[/img] [url=http://wiki.garrysmod.com/page/Player/SteamID64]Player:SteamID64[/url] which you can store in a bigint. I don't think this would have a huge advantage over storing a varchar, but it's something to consider.

[Revenge282]

[QUOTE=Vloxan;52409970]Alright so the character table:
- SteamID (to identify the player who owns that character)
- Model (to know which model that character has)
- Character name
- Note: I want one SteamID i.e one account to be able to have multiple characters

Inventory:
- SteamID (to identify what characters inventory it is)
- Item name (or some other ID)
- Item stack / amount

Let me know if you need more info[/QUOTE]

I need to know whatever you plan on storing in the database. You say you have accounts and characters. What is the significance of an "account" other than it has characters? Can it have anything else tied to it?

[Vloxan]

[QUOTE=Revenge282;52409982]I need to know whatever you plan on storing in the database. You say you have accounts and characters. What is the significance of an "account" other than it has characters? Can it have anything else tied to it?[/QUOTE]

By accounts I just mean I'm using the players SteamID to identify ownership if I've understood you correctly.
For example when a player joins I can use his SteamID to identify which player information I need to serve. This works because I'd select the rows with his SteamID in and then get back info within the row like the name of the character and model etc to display to them and set serverside.

A SteamID could have all kinds of data tied to it like bans etc.
A character could have stuff tied to it like it's inventory, properties it owns and more.

[editline]28th June 2017[/editline]

[QUOTE=SFArial;52409972]There's also [img]http://wiki.garrysmod.com/favicon.ico[/img] [url=http://wiki.garrysmod.com/page/Player/SteamID64]Player:SteamID64[/url] which you can store in a bigint. I don't think this would have a huge advantage over storing a varchar, but it's something to consider.[/QUOTE]

Yep I am storing it in a big int using SteamID64

[Revenge282]

So you can do this then:

CREATE TABLE IF NOT EXISTS `somethingsomethingrp`.`characters` (
  `steamid` BIGINT UNSIGNED NOT NULL,
  `charid` INT UNSIGNED NOT NULL,
  `name` VARCHAR(45) NOT NULL,
  `model` VARCHAR(100) NOT NULL,
  PRIMARY KEY (`steamid`, `charid`))
ENGINE = InnoDB

CREATE TABLE IF NOT EXISTS `somethingsomethingrp`.`inventory` (
  `charid` INT UNSIGNED NOT NULL,
  `item` VARCHAR(45) NOT NULL,
  `quantity` INT UNSIGNED NULL DEFAULT 0,
  PRIMARY KEY (`charid`, `item`),
  CONSTRAINT `fk_characters_inventory`
    FOREIGN KEY (`charid`)
    REFERENCES `somethingsomethingrp`.`characters` (`charid`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB

Basically, SteamIDs and character IDs will be combined to be the primary key for the characters table. This means that any combination of 'steamid' and 'charid' can only occur once, so you can't have a SteamID with the same character ID should that ever happen somehow.

You're definitely going to have to store more information than this to do anything productive, but expanding should be relatively easy, just pick the right data types for you columns.

[Vloxan]

[QUOTE=Revenge282;52410078]So you can do this then:

CREATE TABLE IF NOT EXISTS `somethingsomethingrp`.`characters` (
  `steamid` BIGINT UNSIGNED NOT NULL,
  `charid` INT UNSIGNED NOT NULL,
  `name` VARCHAR(45) NOT NULL,
  `model` VARCHAR(100) NOT NULL,
  PRIMARY KEY (`steamid`, `charid`))
ENGINE = InnoDB

CREATE TABLE IF NOT EXISTS `somethingsomethingrp`.`inventory` (
  `charid` INT UNSIGNED NOT NULL,
  `item` VARCHAR(45) NOT NULL,
  `quantity` INT UNSIGNED NULL DEFAULT 0,
  PRIMARY KEY (`charid`, `item`),
  CONSTRAINT `fk_characters_inventory`
    FOREIGN KEY (`charid`)
    REFERENCES `somethingsomethingrp`.`characters` (`charid`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB

Basically, SteamIDs and character IDs will be combined to be the primary key for the characters table. This means that any combination of 'steamid' and 'charid' can only occur once, so you can't have a SteamID with the same character ID should that ever happen somehow.

You're definitely going to have to store more information than this to do anything productive, but expanding should be relatively easy, just pick the right data types for you columns.[/QUOTE]

Thanks I'm going to go ahead and read up on all these key words as I'm not incredibly familiar with them all.

And yep I will have to store more info to do anything productive, I was just looking to get some starting help. Thank you :)

[editline]28th June 2017[/editline]

Hold on, unless I've overlooked something wouldn't steamid being a primary key cause an issue, don't they have to be unique in every row?
With multiple characters you'd have the same SteamID in multiple rows.

[Revenge282]

[QUOTE=Vloxan;52410083]Thanks I'm going to go ahead and read up on all these key words as I'm not incredibly familiar with them all.

And yep I will have to store more info to do anything productive, I was just looking to get some starting help. Thank you :)

[editline]28th June 2017[/editline]

Hold on, unless I've overlooked something wouldn't steamid being a primary key cause an issue, don't they have to be unique in every row?
With multiple characters you'd have the same SteamID in multiple rows.[/QUOTE]

Ask about the keywords if you have questions.

As for the primary key thing, I made a mistake and was thinking of a totally different design than what you had. Sorry...

Here is what you are looking for:

CREATE TABLE IF NOT EXISTS `somethingsomethingrp`.`characters` (
  `charid` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `steamid` BIGINT UNSIGNED NOT NULL,
  `name` VARCHAR(45) NOT NULL,
  `model` VARCHAR(100) NOT NULL,
  PRIMARY KEY (`charid`))
ENGINE = InnoDB

Character ID's will be the primary key, and will autoincrement as each row is added.

[Vloxan]

Correct me if I'm wrong but when a player joins would I look for their SteamID like so

SELECT * FROM CHARACTERS WHERE steamid = xxxx;

I was under the impression that you would use the Primary Key to look that info up but I guess it isn't then?

I would at least initially have to use their SteamID to identify all their characters.

[Flapadar]

[QUOTE=Vloxan;52410126]Correct me if I'm wrong but when a player joins would I look for their SteamID like so

SELECT * FROM CHARACTERS WHERE steamid = xxxx;

I was under the impression that you would use the Primary Key to look that info up but I guess it isn't then?

I would at least initially have to use their SteamID to identify all their characters.[/QUOTE]

Primary key is how you prevent duplicates, you can search on any column you like. You should create an index on frequently searched column.

[Revenge282]

[QUOTE=Vloxan;52410126]Correct me if I'm wrong but when a player joins would I look for their SteamID like so

SELECT * FROM CHARACTERS WHERE steamid = xxxx;

I was under the impression that you would use the Primary Key to look that info up but I guess it isn't then?[/QUOTE]

You don't have to. You could make the steamid column a regular key, or add it to the primary key if you really wanted to (though there's no need for uniqueness on steamid). To be honest, the speed difference would likely be negligible in your use case.

[Vloxan]

[QUOTE=Revenge282;52410143]You don't have to. You could make the steamid column a regular key, or add it to the primary key if you really wanted to (though there's no need for uniqueness on steamid). To be honest, the speed difference would likely be negligible in your use case.[/QUOTE]

Alright I'm going to set the SteamID as a index as that is how I will get the rest of the row data, seems like it's fine to do this. Again though I wouldn't wanna set the SteamID as a Primary Key as I need multiple rows to have the same SteamID in my character table.

[Revenge282]

[QUOTE=Vloxan;52410169]Alright I'm going to set the SteamID as a index as that is how I will get the rest of the row data, seems like it's fine to do this. Again though I wouldn't wanna set the SteamID as a Primary Key as I need multiple rows to have the same SteamID in my character table.[/QUOTE]

Setting two columns as a primary key doesn't work like that.

[B]charid : steamid[/B]

100 : 12345 (Allowed)
101 : 44422 (Allowed)
102 : 10001 (Allowed)
101 : 10001 (Allowed, because the charid 101 was only paired with steamid 44422, and steamid 10001 was only paired with charid 102)
104 : 12345 (Allowed)
102 : 10001 (Not allowed)

Combined unique/primary keys look at the combination as a whole, rather than the individual components for uniqueness.

[Vloxan]

[QUOTE=Revenge282;52410190]Setting two columns as a primary key doesn't work like that.

[B]charid : steamid[/B]

100 : 12345 (Allowed)
101 : 44422 (Allowed)
102 : 10001 (Allowed)
101 : 10001 (Allowed, because the charid 101 was only paired with steamid 44422, and steamid 10001 was only paired with charid 102)
104 : 12345 (Allowed)
102 : 10001 (Not allowed)

Combined unique/primary keys look at the combination as a whole, rather than the individual components for uniqueness.[/QUOTE]

Oh wow I just tested it, that's awesome dude thanks.

I can use the SteamID to get the row data when the player has joined and stuff and from what I know the primary key speeds up the look ups. Super!

FOREIGN KEY (`charid`)
    REFERENCES `somethingsomethingrp`.`characters` (`charid`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)

From what i can tell this bit enforces that you have a reference to a charid that is in the characters table and if you delete the character you also delete the inventory entries?

[Revenge282]

If you don't need the uniqueness check (you don't in the case of the character table), then you would be better served having the steamid column as just a normal key/index. There's a longer explanation, but basically it has to do with the way MySQL is going to create those indexes when you combine two columns.

[Vloxan]

[QUOTE=Revenge282;52410237]If you don't need the uniqueness check (you don't in the case of the character table), then you would be better served having the steamid column as just a normal key/index. There's a longer explanation, but basically it has to do with the way MySQL is going to create those indexes when you combine two columns.[/QUOTE]

Alright I'll set it as a normal index as i don't need a uniqueness check, this only needs to be done for the charid.

[Revenge282]

[QUOTE=Vloxan;52410227]From what i can tell this bit enforces that you have a reference to a charid that is in the characters table and if you delete the character you also delete the inventory entries?[/QUOTE]

Yes. In order for there to be a row added to the inventory table, there must be a row in the character table with that same charid. If not, the insert on the inventory table will fail. Then when the charid is changed in the character table (it never should, but just in case), the charid in the inventory tables will change as well. Should it be deleted, then all rows in inventory with the deleted charid will be removed automatically as well. It's a really handy way to prevent orphaned rows.

[Vloxan]

[QUOTE=Revenge282;52410247]Yes. In order for there to be a row added to the inventory table, there must be a row in the character table with that same charid. If not, the insert on the inventory table will fail. Then when the charid is changed in the character table (it never should, but just in case), the charid in the inventory tables will change as well. Should it be deleted, then all rows in inventory with the deleted charid will be removed automatically as well. It's a really handy way to prevent orphaned rows.[/QUOTE]

Honestly I think that's absolutely everything explained then. I really appreciate you taking your time and to help me on this, thanks for being so friendly :)