Doranwen

I am the most ridiculous geek sometimes. Or nerd. *points to icon* Or both.

Anyway, so it was a few days shy of two weeks ago that my brain started to fixate on the fact that I have no real good way to organize the fics I have saved. On the hard drive, I order by fandom, then under that, by author. Authors with only one fic are usually loose in the Fandom / Fics folder whereas those with more than one get their own subfolder. This works… as long as the fics aren't co-authored, and I remember who wrote the one I want to read (or at least the title - hard drive search does work).

However, I've got enough saved that sometimes I can't remember who wrote the story, and I don't always remember the title. I also find it annoying that I can't see all co-authored stories in one place. (And how do you find a fic when you remember "it was that one where such-and-such happened", or what if you want to read "all the fics in this fandom that happened at that point in canon"? Impossible, with that organizational method.)

AO3 is not a proper solution for two main reasons:

1) AO3 bookmarks are for AO3 only. Most of the stories I have saved are from other sites (depends on the fandom, but the vast majority of Rookie Blue, for instance, were posted on Fanfiction.net, not AO3, and the X-Men fics I love the best were on separate fanfiction archives). It doesn't let you bookmark any fic anywhere on the 'net. (Its tagging is also pretty limited so far, I hear - I haven't even played with it all that much.)

2) AO3 tags (and Fanfiction.net, for that matter) are assigned by the author. Which is all well and good - for a fanfiction archive, I would expect no less. But what I want here is a site that would let the readers tag things as they saw fit, to let them find stories easily by tags, even shared tags.

Since no site exists to handle those things… I turned to an offline solution. But there wasn't anything premade, so my brain went "well, why don't I create a database to do this?" I had no idea what I was getting myself into, lol.

I really have intended to write this past week! Or work on projects that I need to do to earn me $$ or be useful in my earning of money. (My greatest apologies to my alpha reader - I promise I will get back to writing the Rookie Blue fic now!) But this wouldn't let me go, so in the spare time I found I started reading some tutorials that walked through designing a database, and learning the basic concepts - primary and foreign keys, data normalization, etc. I drew up a fancy UML diagram with Dia - and later dropped a half dozen tables from that.

And then I started to build the database itself, using LibreOffice. I started very simple - three tables - and worked my way up. Pretty quickly I had to ask for help on the form, because characters proved tricky; I wanted to be able to assign characters to a fandom, yet also filter them when assigning them to a fanfic (no point in having Aragorn appear in the dropdown when you're entering info about a fic that is only in the X-Men fandom, for instance). A very kind volunteer on the OpenOffice forums assisted me with working through the logic, ultimately writing a macro for me to use (which I later copied and edited to use four different times in all - authors, ships, and time/settings also needed that filtering capability) to solve that issue.

About a week and a half after starting, I now have a database with fifteen tables (multiple ones that use compound primary keys as mapping tables between many-to-many relationships) and two queries that are used in one very complicated form (it has lots of pretty refresh buttons, lol).

My database stores the following data:

- Title
- Author (allows for co-authoring)
- Fandom (allows for crossovers)
- Characters (filter by fandom - a character must be unique to fandom - no limit to number that can be assigned, nor is there any order to them - they'll appear in the order the characters were added to the database in the first place)
- Relationships (not associated with characters but IS associated with fandom - can assign a relationship to multiple fandoms to accommodate crossover relationships - ships will appear in the order they were added to the database in the first place)
- Time Setting (associated with fandom, must select only one per fic - with crossovers, select the primary fandom to use and choose a time setting from that fandom's list)
- Theme (basically "other tags" on AO3, not associated with fandom, can select multiple per fic - will be ordered in the order they were created in the database in the first place)
- Wordcount
- Special Notes (250-char freeform, good for summary or whatnot)
- Series (optional, not associated with fandom)
- Series Number (for noting which fic in the series the current fic is)
- Rating (currently uses the classic movie G/PG/PG-13/R/X set, must choose one - includes a U for Unrated)
- Web link (to store where it's found online)
- Offline location (to store folder where it can be found on hard drive)
- and an assortment of checkboxes such as WIP, Favorite, etc. (can be used to help with fic recs "does this fic fit this criteria" - checkboxes meaning yes/no are the only options, of course)

I already have two queries created to pull data out - one that pulls all the fanfics that have an offline location stored, and another that pulls all the fanfics that are over 1000 words (I was testing the capabilities), with three versions (depending on whether it's sorted by author, by fandom, or by wordcount).

And I started to think - would anyone else want this? The hard work is already done. I have a few checkboxes that can be removed or changed out to fit whatever one wants, and if someone's picky about the ratings, I can go to the K/K+/T/M/A set from Fanfiction.net.

If this interests you at all, let me know. I'll be happy to tweak the checkboxes as desired, change the max length of the special notes field if necessary (it's really not hard), and change the ratings if you really want it. I have an empty version of the database that I can upload anywhere - but the checkboxes in there are very much personalized so I'd want to give you the ones you want. And if you want it set so that one field is much longer than I might have it, I can easily tweak that (or just give it to you to tweak if you know what you're doing). (Obviously, if you want other sorts of searches - queries - set up, you'll need to either figure it out yourself or ask me or a database-savvy friend to set them up for you.)

You'll also probably need a guide to using it… which I'll write only when I know someone actually wants it, because it'll take a bit to write! To add a record with a new author, for instance, takes using four different parts of the form (lots of subforms) to get from "add author" to "this is the author(s) assigned to this fic", due to the complexity of assigning to a fandom and filtering the choices - and if you don't know where to start, you'll get frustrated trying to figure out how to work it. There's a definite flow pattern that you have to use and it's not obvious from looking at the form what it is.