YGO Collectible Database

Background

I began this project as a functional way keep track of the cards I own, whether valuable or not, to allow me to easily trade with others. As I currently own over 5000 cards, it was difficult to keep track of the individual cards when looking to trade with others when meeting. As you might imagine, spending over 30 minutes to an hour digging through 5000 cards to find the specific one you're looking for is extremely frustrating. After I had made it, I realized I wanted to know more about my spending habits, how my pull rates fared, and how much monetary value I had gained or lost (mostly lost) from my hobby. My database is still in development and will be for a while as I keep finding new data I would like to track.

While typically, best-practice would have to have the database well-designed before put into production, as this is a project that does not require constant up-time or usage from others, I chose to begin with only a basic concept in mind. I was unsure of what additional features I would have time to include or would want in the future.

Hosting

My RMDBS uses SQL server hosted my home server running in its own Proxmox lxc container. To access my database, I use Azure Data Studio and SQL Server Management Studio when managing data or queries and SSH into my container if I need to direct access . To give myself a simple front-end for user outside of my network on the day to day, I used a low-code development CRUD App called Budibase.

Modelling

Below is the "physical" model which I had originally created a year ago, although a lot has changed now.

As you might notice, it is quite barebones. Many features were included and thought of and included later on as my project grew. While typically, in a production environment, it is advised to have a conceptual, logical and physical data model before developing, I figured I could be more lenient as the only “stakeholder” was myself!

The master Tables consist of ~

Cards
CardSet
Storage
CardtoSet

To display data on each card within my database, I have found a publicly available dataset, which has an in depth description of each item within the over 35000 different options that exist. I am currently working on a python script that is able to scrape card pricing data so I am able to get a rough idea of how much my collection is worth.

Data Integrity

While this is less important in my case due to the basic schema and as the only user (me) understands the data types, attributes and relationships within my database, I have still included a few features to prevent mistakes when doing the data entry. I would like to include more constraints and triggers in the future as a few a of my friends seem interested in having a digital library of their collection as well.

I currently have a constraint that prevents duplicate card entries within the same storage, and as currently, queries within my CRUD app update the specified rows. I need to allow duplicates between other storages, as I may want to have copies within my binder for travel.

I also have a trigger that only allows CardIDs and SetIDs that match an entry in my Card-to-Set table to be entered. Cards come from specific sets, and I would like to prevent a mismatch there when making entries.

Performance

I am would like to use my database to learn how to implement performance features such as non-clustered keys and sharding/partitioning (between my home server and my VPS) in the future, but since I only have about a total of 200000 rows stored, performance is not an issue.

I currently have clustered keys set card name and set name within the "Cards" and "Set" tables respectively, as they are the columns that are queried and displayed within my CRUD app.

Future Plans

I am hoping to use this database for myself, friends and any acquaintances who would like to have a digital library of their cards stored (and also have the patience to do the entry!). This is a project that I started for fun to practice my database and as such, I am looking to add a bunch of features that may provide negligible benefits functionally, but would help me develop my skills.

The next few updates I am looking to make would be to migrate the users table to my database (as it is currently a build-in feature on my CRUD app), include a records table displaying user entry data (date, time, user, entry ID, etc.) and an actual transactional table which saves than transaction and cascades the updates to my collection table, so I have a historical record of entries as well.