F Lucas wrote:"If A determines B, B is functionally dependent to A".
As in, no record with a different A can have the same B for it to be functionally dependent.
So, what I probably should be doing is
Like, instead of using flags, I can just use the fact that the record exists in a specific table to get the information needed?
F Lucas wrote:is_online really is not supposed to be in the database, but I am making a page with django and I am not sure if I can check if there is an ongoing session with a specific user.
name: varchar (PK), Its the username of the account. Maybe I should be using a separate ID for the PK, but since I could enforce unique usernames this way, I figured I'd just make this the PK.
password: varchar (cyphered before added to DB), The password.
queue_status: tinyint, This is either 0(Not in queue),1(In queue waiting for match),or 2(In queue but not available for matching.)
queue_time: time, The amount of time this player has been in the queue. Players with the bigger queue_time are prioritized to find a match quicker.
name(FK): The name of the player in game. Unique, not nullable.
game_info: a varchar that will hold a JSON, not too big in size, that holds all the variables needed for the game.
ongoing_time: Time this match has been going for. I might have to terminate matches that go on for too long and delete records related to it. Nullable, because it is null for finished matches.
result: A JSON that holds information about a finished match. Contents vary depending on the Ruleset, so again, I cannot make definite fields to hold the values. Nullable, because it is null for ongoing matches.
content: JSON to be read by the backend code. unique.
The problem with natural keys is that they might take up more space in your database if the values take up more than a few bytes.
I hope you're using Django's authentication mechanism, and you're not building your own.
Why have a 0 status at all? Just remove the entry when a player isn't queued up.
Also, if your database supports it, use enums instead of integers for columns like this. It's much nicer to read something like 'queued' and 'ready', instead of 1 and 2.
Don't store an amount of time, you can't reliably update it.
I hope it only holds all variables related to the specific player.
For larger text formats like JSON, use CLOB instead of VARCHAR. Or consider serializing your game state to a binary format, and saving it as a BLOB.
It's probably highly unlikely that two identical rulesets will get added to the table, and even if they do, is it that big a deal?