write.as

Journey Start

My Background I recently started my first job as a Jr Software Developer back in August. I have a basic understanding across a lot of different languages such as Java, Kotlin, Javascript, C++. As well as various different tech stacks such as Spring, React, Angular, Flask, KTor, OpenGL, etc etc. I'm hoping to deepen my knowledge of databases in particular with this project.

Objective: Model a complex dataset across different database models, and to document my learnings and experience. In doing so I hope to learn more about what data models work well for different data sets, to document which performed better for different types of query, and general development experience. I'll start off with an initial iteration of the data, and continue to add complexity to it as I progress. This will be a project that I work on whenever I have time/feel motivated, as such updates will be whenever I feel it's worth writing another post on something.

The Data: The goal is to implement a fantasy inventory system modelled after fantasy mmos/stories. There will be players, who can have different types of custom weapons/armours. The equipment can have various different types of modifications/runes/enchantments which will affect the stats of the equipment. There will be different types of equipment that can be equipped by specific player classes.

On top of that there will be an economy system. Blacksmiths can create/repair equipment, and will have their own inventory of equipment in stock, as well as equipment currently being repaired for players. There are also merchants who deal in the trade of equipment. All merchants/blacksmiths have specific locations around the world, which take time to move equipment around.

All equipment can belong to a specific player, eventually I might implement it so that a piece of equipment can be traced through owners (current owner, who they bought it from, so on and so forth until it makes it's way back to the original smith who created it.)

The Tests We'll need to cover a wide range of queries on the data, such queries might include the following: – Querying a specific players inventory (perhaps they have an extremely large inventory). – A piece of equipment's history (who's owned it, where it's been sold/which black smiths it's been to for repair.) – The status of all weapons of a specific type – Each piece of equipment's stats, owner, current location etc etc – Rapidly editing large amounts of relating data (must remain consistent, should have same results every time) – Comparing speed of queries, edits, add/deletes across different database types. – What happens if I need to add/remove columns on tables that have large amounts of data. – How does the database handle such large changes? – Document time taken to perform these changes.

Other Things to Investigate I'd like to look into best practices and such, and pros and cons to different approaches, such as is it better to handle constraints via the code or let the database handle things such as foreign key restraints. Do database side constraints have a significant impact on performance? Can you provide the same guarantees from code side checks?

The Tech Stack The plan is to write a single front end UI using React w/Typescript and communicates with the backend via GraphQL. There will be multiple backends written in Java with eclipse jersey, one per database type. Ideally the backends will be as close together as possible to avoid having to rewrite too much.

Why did I pick this particular stack? It's what we use at work and I want to get more experience with it. If I'm feeling ambitious I might eventually see how much the specific backend language/framework used acts as a bottleneck and try some other options, perhaps a Rust backend.

Databases: – MariaDB (Might try different engines to compare.) – ArrangoDB – MongoDB – CassandraDB – Neo4J

Initial Steps I'll be starting off with a MariaDB backend since it's super commonly used, and we use it at work. Once I'm satisfied with the result (and assuming I haven't gotten bored of the project by then) I'll probably move on to either Mongo or Neo4J.

Below you'll find the first revision of table design I came up with for MariaDB. Already I'm seeing it will need to be changed before anything is implemented due to a lot of duplication of data across Weapons and Armour, even though they functionally serve different purposes. I'll also need to be able to reference things under an items table regardless of items type. I'll probably include a separate items table that each weapon could have a foreign key that points to. I'll probably post a new revision of this soon.

Weapons – ID – Int (PK) – Name – String – Type (FK)

WeaponTypes – Name (PK)

WeaponStats – WeaponID (PK/FK) – Slash Damage – Piercing Damage – Blunt Damage – Fire Damage – Dark Damage – Lightning Damage – Magic Damage – Weight

RequiredStats – WeaponID (PK/FK) – Strength – Dexterity – Intelligence – Faith

Armour – ID – Int (PK) – Name – Type (FK)

ArmourTypes – Name (PK)

ArmourStats – ArmourID (PK/FK) – Slash Resistance – Piercing Resistance – Blunt Resistance – Fire Resistance – Dark Resistance – Lightning Resistance – Magic Resistance – Weight

Hero – ID (PK) – Name – Level – XP – Strength – Dexterity – Intelligence – Faith

Conclusion I'm hoping to be able to work on this more regularly soon and will start writing code for this soon. So far all I really have setup is a maven project. I look forward to undertaking this journey, and hope that my own learnings may also be useful for other people!