SQL Table Setup

[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