Hello again!  I'm back to discuss the progress on the site rebuild.  For this entry I'm just going to talk a little more about the issues I uncovered while performing data migration into the shiny new database.

Before I get into too much details, I would like to say that the migration efforts are actually coming along quite nicely and I think I'm nearing the final steps of moving everything over with the games (aka games & variants) table being the last one I'm in the process of tackling.

Now let's get into the issues during the migration itself and how some of them were resolved.  First off, this was a fairly big undertaking.  I already talked in detail about the issues with free form fields in the data in the last dev log.  All of the company, person, genres, etc have been standardized in the new table.  One other free form field proved to be much more complicated - control types (controller on the game page).  This was so varied it was hard to even do accurate fuzzy matching to get good results.  The original plan was to try to map the listed control to an actual accessory, for instance "standard controller" would map to the Dualshock 2 controller for a PS2 game.  This led to me adding a field in the database to set a default controller for the console family.  This works for the most part but any specialty controllers get much harder to map without manual intervention. Furthermore, consoles like the 32X and Sega CD add-ons didn't have their own accessories - just using the ones of the genesis.  That let to another table to support compatibility mapping between consoles.  Of course that's not the last of controller/accessory compatibility, there are instances where some accessory only work with specific consoles within a family.  One example is the TurboDuo game pad is not compatible with the original Turbografx-16, this needs another table to support mapping what accessories work on individual platforms within a console family.

This might all sound a bit frustrating or counter-intuitive but it makes the data much more robust while also eventually allowing more searching, sorting, and filtering options for the new site.  Having the proper relations between all the data points is a good practice for this kind of data and will also cut down on all those errors and inconsistencies found in the existing data.

Currently, most of the data has been moved over - consoles, accessories, companies, people (shout out to ApolloBoy for helping with the person data).
I have been writing an application that will take JSON files of the data in the existing database and generating SQL queries to run in the new database.  I've used it for all of the migrations so far and the game data is no exception.  However, its also the most involved table.  For each game or variant, up to 15 different tables need to be updated.  Its a lot but ideally in the future, this will open up a lot of possibilities.  For instance, we'll be able to search for all PlayStation 4 games released in the US that are 1st person and metroidvanias.  Or we can find all the games that Square and Nintendo collaborated on.  Further, we can add things like collection stats beyond just how many games for a platform you own.  There are many more similar examples that I hope we'll be able to support in the long term.

So what's next after data is migrated?  I'll be able to start putting together some back end APIs to allow access to this data and work on getting actual site mock-ups on how things can look.  Once some mock-ups are created, I'll be sharing them in the discord for community feedback.  Its still a little early but once some of these pieces are functional, it'll also be easier to get the community involved by verifying data and looking into proposed features.  There is still work needed to migrate the forum pieces as well but I'm mostly focusing my efforts on the main collection tracking portions to start with.

I hope this helps give a glimpse into what's happening behind the scenes.  If there are any questions/thoughts/concerns you can put them in the comments below or reach out to me in discord.

Permalink | Comments [1] | Digg This Article | Tweet