Game update, but this is more conceptual than anything. I also need to use this as a sounding board because I’ve run into a conundrum that I am having trouble working out.
This is about commodities: how they are made, calculated, bought, and sold.
Means of Production
I have written a manifesto on how I would like players to interact with the commodity stream at a very low level. It’s not 100% player-driven economics, but it’s close, and it gives players agency to fill in the gaps within the marketplace by deciding what to produce and sell. But this is also end-game content because of the complexity and how non-flying-around-in-space it is. Before I can get to that point, then, I need to find a way to get commodities into the system via automation.
The idea is that each system with a station — not all systems have stations — has a “native” populations on temperate worlds, and outposts on other worlds in the system. These populations are making stuff, starting with food and necessities, and then moving on to more complex items. Each planet in the system has a “specialty” which is a classification of goods such as “agriculture” producing soybeans, meat, and dairy, or “mining” such as iron and silver. Some planets generate advanced commodities like construction equipment or wine or computers.
Of all of the units produced, some are held in reserve to simulate “what the population needs”, and the rest are sent up the well to be sold at the station where players can trade. By setting up this kind of production pipeline, planets can be affected by events that alter their production, which can lead to other events and ultimately enhance or disrupt the inventories available at orbital shops. At the end of the chain is the price calculation that takes into account the total amount produced, how much is in reserve, how much inventory the shop has, and regional, system, or planetary event modifiers. That’s cool.
What’s not cool is how the hell to store all of this data! I don’t yet have a comprehensive list of commodities, but I envision having at least 50 individual items in total. I calculated it that if I generate 10,000 systems (100×100) and if each system did have a planet and a station, that would mean I’d have to store ((50 * 2) * 10,000) = 1,000,000 individual item records in order to track inventory of a single item at a specific location. While I won’t have 2 locations per star system, I will have cases where I might have 5 locations in a single star system, 3 in another, and only 25 items to track. Making matters worse, if I ever get to the point where I want to introduce player-run manufacturing according to the plan I came up with, that 1,000,000 number could explode.
I have come up with a few ideas, none of which I consider to be Any Good, Really. At the base of all of them is the idea that each location would store the commodity ID and quantity at minimum, because it’s the quantity that’s malleable here. So, here’s the crappy list of ideas:
- Create a List<T> of these small objects, serialize them to a JSON string, and store them with the parent object (planet or shop).
- Store the small object as individual records in a table. This is where that 1,000,000 record number was theorized.
- Create an inventory table and have one field to represent one commodity item. Naturally, this is a Very Bad Idea.
- Save inventories as files with the file system in JSON format.
What I think I’m going with is a kind of hybrid between the first item and the second item. Hear me out.
Planetary production is going to be an off-cycle thing. Every 24 hours, a dedicated app is going to run that manages the Universe. This will load all of the planetary JSON, update the values, and update the table. Part of this will be to take a percent of the production in reserve, with the rest going into the inventory of the shop in orbit. Each shop in orbit will also have a JSON string, so we’ll have to pull that as well and update those totals with the new quantity from the planets in the system.
How Does This Happen?
The problem was always “how to surface this info to the player?” because all of it is academic unless the player knows about it.
When a player hits up a shop, we’ll load the JSON from the shop into a “shopping cart” object. This will be a collection of “shopping items” which are a mix of the commodity identifier, shop quantity, player’s cargo quantity, an indicator showing whether the shop buys or sells that item, the cart quantity, and the price. This cart is calculated and written to the database with one shopping item per record. That means if there are 50 commodities defined, each player will have 50 records in a shopping cart table. I suspect there’ll be far fewer players than there will be star systems, so I’m OK with this kind of volume.
The cart represents a snapshot in time that belongs to the player. When she clicks an item, we’ll get that commodity info from her shopping card back end and display it to her. If she wants to buy items, she can move a quantity from the shop to her cart. If she walks away without buying, then the shop inventory is not affected because we haven’t sealed the deal. If she completes her transaction, we debit her wallet for the total cost and subtract her cart inventory from the shop inventory. This WILL require us to load the shop JSON, make the change, and write it back, but reading and writing that field will only happen A) when the shop is first opened, and B) when a transaction is officially approved. That should minimize the number of times we have to deal with this weird JSON situation. What I need to be aware of, however, is how to abandon a cart. Since this cart will feed the user’s view, I can’t have it sitting around forever else we present Bad Data to the user. From another direction, since this is a snapshot of the market made specifically for the user, it cannot be used to represent the state over a long period of time; if someone buys 50% of the inventory while a player is out to lunch, the player should not see their original quantity or price now that it’s been significantly altered in their absence. I’ll have to time and date stamp the cart and expire it after a reasonable period of inactivity. The question then is, do I merely empty the fields, or do I remove those records from the database and keep the size slim?
If you have any suggestions for how to handle a situation like this, please please leave a comment. I’m open to suggestions.
 SQL Server actually supports dumping raw JSON into a field, and you can query it directly. Using JSON_QUERY, I could write SQL that does a lookup of a JSON key without having to drag the whole string into an object. As cool as that is, it’s not yet supported by the Entity Framework, so I’d have to write raw SQL to make it happen, and I’m not sure I really want to go that route unless I absolutely have to.