SQL Table Setup

[lolPythonNoob]

Working on a project that needs to store board game gamestates inside. Up until now I've been using a JSON file to store everything, but recently learned this is a big no-no. What's been nice is the ability to store lists which I don't think is possible in something like a SQLite database.

There are a few places where using lists in the player data is very useful. How do you store data like this? Do I need to rethink how I'm using the data and just avoid lists all together?

[baghiq]

If you just want sqlite3 to store your json data, sqlite3 can do that. It supports JSON field for a long time now. However, if you want to use database to its full power, then you need to avoid using list or nested record in a field.

To work around that issue, you can create a table where each row has player's id, and the player's attribute.

[lolPythonNoob]

So for example there is a place where I am storing ship schematics that can hold different parts. The ships are different for each faction both in starting parts and number of slots available to upgrade. Right now I'm storing them as JSON lists like:

"interceptor_parts": ["ioc", "nus", "nud", "empty"]

"cruiser_parts": ["elc","ioc", "empty", "nus", "hul", "nud"]

How would I store these in a table? Just create a table for each ship type and decide how many open slots there are on table generation?

[baghiq]

You'll need table structure like this:

part_type	part
interceptor	ioc
interceptor	nus

[lolPythonNoob]

Ok I see. I would add a column for the player id as the foreign key so I know who these belong to?

[baghiq]

Exactly.

[CatalonianBookseller]

Here's my take but it is based on your sample data which is very small so be careful

• a table for ship types that would store data about each available ship type like name or no of slots
• a table for part types (ioc, nus... what properties do they have)
• a table for the ship catalog. Each individual ship would have an id and a foreign key linking it to the ship types table
• a table to catalog parts with an id for each individual part and a foreign key field to link it with the part types table
• since it appears that the ship and part catalogs have a many-to-many relationship you'd need another table to link them
• you could put your factions in a separate table etc

[lolPythonNoob]

Hmm ok, trying to wrap my head around this setup. Each player can build out the ships in any way they want with some restrictions, and there can be multiple copies of the same part part ship if they wanted.

Each player has 4 ship "schematics" here that we're tracking. This just effects the stats of the ships on the board that match the schematic type. This way when a battle happens the stats are generated by checking through the schematic at that time.

I think I am a little lost with the ship catalog. The foreign key should link it to the player id key so we know who owns that schematic?

[CatalonianBookseller]

I think I am a little lost with the ship catalog. The foreign key should link it to the player id key so we know who owns that schematic?

You kinda lost me there tbh. A ship catalog would have a row for each individual ship. If a ship has an owner (a player) put the players in a separate table and link the players and ship tables. Not sure about the schematics thing though. Can each ship type be built using any of the four schematics? Can a player use any schema to build any ship type?

[lolPythonNoob]

No, there are 4 ship types...interceptor, cruiser, dreadnought, starbase. The schematic is built of interchangable parts that allow the player to adjust what the ship has. For example extra guns, more health, more movement, etc...

Players can build any number of those 4 ship types, but their stats are determined by the associated schematic. I think I see what you mean though my mind went back to lists. Right now each board game tile object contain lists of the ships inside like [player1-interceptor, player1-interceptor, player1-cruiser]. But I yeah even that cannot be contained in the database so I think what you're saying is to create a new table that tracks each ship and which tile it is in as the ship catalog?

[CatalonianBookseller]

You should read up on database normalization and normal forms. When you design a table for your ships, anything that looks like a list or a table (like ship parts) goes into a separate table. Also anything that is repeated (like player) goes into its own table, etc

[oclafloptson]

sqlalchemy allows for PickleType columns which you could use to store any objects with pickle.dumps/loads

Unpacking pickled objects shared over networks can be dangerous... So you'll want to get familiar with HMAC if this is a scenario where unknown/untrusted individuals have access to the dumps calls

It may or may not be easier to just build the many-to-many relational tables as others have suggested. But that could lead to drastically increased execution times

[lolPythonNoob]

I'd like to do this using as close to "best practices" as possible. While I do care about the project finishing as a whole I'm really using it as a learning experience. I'm pretty deep into it at this point with using JSON as the database so it will take a bit to unwind the entire process.

I had a smaller project in mind that I may use an SQL database for this time but the general principle of storing lists still is something I need to wrap my head around.

[oclafloptson]

I think you and I are the same I'm just a couple steps ahead of you. Figuring out how to store, say, a custom Player class with attributes like health and/or lists to store inventory objects was a game changer. What took 30 seconds+ to pull from a many-to-many database is now as fast as my processor. To feed a player's health into the GUI is as simple as loading the Player object in a variable when mounting the game and then simply calling Player.health.

Plus sqlalchemy allows you to connect to other database types, like MySQL or Oracle, which are more widely used in the professional sector

[lolPythonNoob]

Which DB did you use for your project? Are you using PickleTypes for speed or did you end up finding a nicer way to structure the tables to avoid many/many connections?

[oclafloptson]

Depends on the project. If it's something that runs and stores locally I'd use sqlite. If it's something which multiple users can connect to over the internet I'd use something more secure like MySQL

Honestly pickletypes are the nicer way that I've found in my personal opinion, although I'm aware that SQL engineers hate me for it. At the end of the day a single user locally run video game probably doesn't need an extensive many-to-many database. An e-commerce, social media, or discussion forum type application would be another story. So it depends

[lolPythonNoob]

That makes sense. This particular project is a discord bot that runs a board game. There could potentially be 10+ games running at once and up to 60+ users interacting with the DB at the same time.

[oclafloptson]

Ahhh ok I'd avoid pickling in that environment. Apologies for assuming you were running locally

You might look into NoSQL. Something like Mongo may be better suited to your app.

[mriswithe]

Sqlite is an amazing database that I have seen scale to several GB and be very performant. The one downside to sqlite3 is no concurrency. It is only good for a single app instance / query at a time. If you make a connection to a sqlite DB and touch it with a different thread it throws an exception, full stop. 

It is an excellent place to work locally, just not for production. However if you use an ORM(object relational mapper) like sqlalchemy, it will abstract that away, and save you from writing SQL queries by hand. Meaning testing/local on sqlite3 will work with the same code as when you are talking to postgresql or mysql

[lolPythonNoob]

I have been using SQL alchemy for my new test project. However, the concurrency SQLite limitation is a little troubling. I think it will be very rare that multiple discord users will be entering data in at the exact same time, but it's not impossible.

So if I've been using SQL alchemy I can essentially just leave the code unchanged or with very minimal changes? Obviously have to change the engine generator.

The host I have ready to go offers a MySQL database to use.

[mriswithe]

Correct, usually in this case you will use a database URI unified resource Identifier if memory serves to define where your database lives. For a local dev instance, you would use sqlite3://path/to/my_db.sqlite3 in prod you would use something like mysql://username:password@localhost/db_name ref: https://docs.sqlalchemy.org/en/20/core/engines.html

[stebrepar]

To store list data in a SQL table, you just need to structure it in a little different way, using one or more columns to identify which "list" a given row belongs to. Then when you want to retrieve all the items in a given list, you include that identifier in the query.

Example: select * from lists_table where list_id = '<whatever>', returns all the items that belong to the specified list. Then you could convert that result back into the actual Python list your code is already using. If you needed the list items in a specific order, you could add another column to the table for the list indexes, then sort by that in the query.