How do I properly build the data structure for the game?

Question

SQL

Good afternoon. I need a database and there was a question about the structure. You can put everything in one table (messages, achievements, perks, bonuses and much more), then I have about 25-27 columns, or divide it into tables according to the whole thing. But then it will complicate the access to the data and the registration of the player (to create these 3-5 tables), plus some kind of problem with joins, it’s not convenient in general. But according to the tables, it seems like they say it will be more productive. How to proceed, which option to choose?

Answer 1

Your problem is not with SQLite, but with knowledge of databases and SQL in particular.

You can put everything in one table (messages, achievements, perks, bonuses and much more), then I get about 25-27 columns

It’s not possible, these are all different entities that you want to expand with new fields and, more importantly, associate them with new entities, for example, give players the ability to group chats, or, for example, sculpt attachments to messages or God knows what else, and you won’t be able to do this after release of your software. You will have to write very sophisticated scripts for data migration, incl. the ones that haven't even changed.
Users is the Users table where there is an ID field that gets a value when a record is inserted and this value does not change.
There are messages, this is the Messages table.
where there is an Autokey that receives a value when a record is inserted, there is a DateCreate index field with the date and time the message was created, there is a Message field - the text of the message, as well as fields From (link to Users.ID) and to (Users.ID)
There are achievements, Achivements table: UserID
field (Users.ID) connection with the one who received the achievement
Date and time field of receipt, achievement
identifier field achievement
description
field field with the achievement picture
Bonuses, Bunuses table: UserID
field (Users.ID) connection with that who received the bonus
Date and time field of receipt,
bonus ID
field bonus description
field field with bonus image
field indicating positive or negative bonus
date and time field of bonus expiration, if NULL then bonus is eternal
Table with perks - Perks:
UserID (Users.ID) field ) connection with those who received the bonus
Date and time field of receipt,
the perk identifier field (PerkID)
is all easily expanded with new fields and, if necessary, overgrown with new links with new changes.
And if you throw everything into a heap, why do you need a database at all?
put everything in json/xml.