When I first built randomanime.org, you would be surprised how simple the database was. Really, it was just a single table. Hey, I was still very new to coding at the time and it was my first database EVER.
shivers
It had a hard limit of 3 genres per anime.. wanna know why? Because the table only had genre_1, genre_2 and genre_3 columns.
Fast forward a little bit and it got A LOT better. I learned more about joins and relational databases. I created separate tables for genres and links and had made join tables for the many-to-many relationships. It was a huge improvement.
Later came the most complicated update I ever did, which was adding support for different languages per watch link of an anime. Add in me wanting to do a manga version of the site, and it got a little messy.
That leads us to now, where I want to design it from the ground up with a few things in mind:
- Single media table with type reference for anime/manga
- Table dedicated to controlling “overrides” to lock/add/remove items
The second table will make more sense later. I am wanting to automate as much of the process as possible and plan on having a timer job that updates media info as well as watch links.
Database structure
This is the result of my planning. I’m still changing little bits of each table’s structure as I start work on the automation and local admin dashboard.
To solve my first goal above - you’ll see three tables: media, anime and manga. The media table has an enum column called type, which is how I know if the media item is an anime or manga. The media table holds all common information between the two, and then anime and manga hold specific information to each type.
The media_field_overrides table is my solution to my second goal. I want a way to control the information without my automated timer jobs overriding my changes. For example, sometimes an anime will have the romance genre.. when there’s barely any romance in it. It’s a little misleading, so I’ll omit it. The table has a field column to designate which field it’s affecting and an action column, which is an enum of lock, add or remove.
The other tables are pretty standard and actually almost identical to what is there now. I have genres and tags, with their respective media_genres and media_tags join tables. Anime specifically has anime_date for the random anime of the day; if the manga side ever gets off the ground, I’ll have a manga_date. Then, since only anime have watch links, there’s the anime_links_audio join table that associates links and their audio.
The supporters and urls tables are simple guys that.. prety much do what they say. urls being the mapping table for the short URLs for the generated lists.
phinxlog is a new one for me. I’m going to be honest, I never used a migration tool on the site before. I figured since I was starting from scratch, I probably should this time.
Fun fun, next steps
Like I said before, I’ve already made small changes to the structure, but I think this is the general idea of how it’s going to look. For example, anime_links_audio was media_links_audio at first.. but I changed it since only anime are associated to links and audio. If I do manga in the future, I can have a separate manga_links table.
media_field_overrides also just had a string type for the field column until I realized that was a terrible idea and made it an enum instead.
Next up, I was working on the data automation side of things. This has been ongoing for a bit and is taking me forever. Mostly because it involves the initial creation of a lot of stuff, but also because I’m a dad and work full-time.
I do think I’m going to change gears a little bit and work on getting my new admin dashboard working. The reason being is because I want to still maintain the data side of the current site.
If I get the admin dashboard setup, I can create temporary handles for the add/edit that write to the old database structure. That way I can keep the data fresh while working on the automation for the new structure.
Welp. That’s it. Until next time!