Brent Dax (brentdax) wrote,
Brent Dax

On database crap

So, I'm working on the databases for my fanfic site.

Specifically, I'm working on the metadata for a story. Basically, a story has a fairly simple table:
  • id (integer)
  • owner (integer)
  • title (tinytext)
  • title_norm (char(32)—this is a version of the title munged to make a pretty URL)
  • description (tinytext)
  • disclaimer (tinytext)
  • note (tinytext—an author's note)
  • date
  • status (enum—in progress, completed, abandoned)
That table only tells a small part of the story, though. There's also a table of chapters:
  • id (integer)
  • storyid (integer)
  • chapterno (integer—0 for prologue, something really big for epilogue)
  • title (tinytext)
  • note (tinytext—a per-chapter author's note)
  • text (probably mediumtext)
Plus there's a many-to-many mapping to fandoms (each story can belong to several fandoms, to allow crossovers). I'll omit the table for fandoms—the only information besides names and IDs is used to fit each fandom into the overall hierarchy for browsing. And finally, there's a many-to-many mapping to tags:
  • id (integer)
  • type (big enum—things like "genres", "eras", "characters", plus "custom" for custom tags applied by authors and "user" for custom tags applied by users)
  • fandom (integer—0 indicates that it's not associated with a particular fandom, for e.g. "slash sex")
  • name (tinytext or char(something))
Basically, most of the metadata is expressed in the tags, which I think will make the search feature quite a bit more elegant. And the "custom" means that people can add specialist tags like "redeemed draco", and "user" means that users can help annotate stories. (I'm not sure about the "user" idea, actually, but I really want something like it.)

The problem is pairings or ships or whatever you want to call them.

They don't fit into the tag concept, because they involve a special relationship between two tags. After all, a story that has "Harry, Hermione/Ron" is rather different from one that has "Harry/Hermione, Ron". So I need to come up with something.

I see two ways to deal with this. One way is to create tags for every possible pairing. Except that this creates the situation best known as "combinatorial explosion". If there are a hundred characters in Harry Potter, there are 10,000 possible pairings—not to mention the people with really eccentric tastes, like "Harry/Ginny/Hermione/Parvati/Padma" (!).

The other way I see to do it is to create some sort of ships table, like so:
  • storyid (integer)
  • shipno (integer)
  • character (integer—links into tags table)
To explain how that works, given a tags table like this:
1character42Granger, Hermione
2character42Lovegood, Luna
3character42Malfoy, Draco
4character42Potter, Harry
5character42Potter, James
6character42Potter, Lily
7character42Weasley, Ginevra
8character42Weasley, Ronald
9character42Patil, Padma
10character42Patil, Parvati

A story with the full set of pairings (H/Hr, R/L, D/G (yuck), JP/LP) would have these pairings in the table:
2411 (Hermione)
2414 (Harry)
2422 (Luna)
2428 (Ron)
2433 (Draco)
2437 (Ginny)
2445 (James)
2446 (Lily)

And that rather scary Harry/Harem thing I mentioned earlier:
2511 (Hermione)
2514 (Harry)
2518 (Ginny)
2519 (Padma)
25110 (Parvati)

That actually would work, but it's a little messy and confusing. Is there a better way?
Tags: fanfic site, hacking

  • Paging madlori (and anyone who knows her)

    An interesting thing just happened on Facebook chat. Lori Summers [2:29:44] Got my message ? Brent Royal-Gordon [2:33:45] I did. Lori Summers…

  • guest post

    kate is the best better than the rest the best the best haikus about kate: kate's my favourite i want to lick her ballsack it would taste so…

  • Practice

    This December, I will have been practicing programming seriously for ten years. That will mark the tenth anniversary of me starting to learn Perl. I…

  • Post a new comment


    default userpic

    Your IP address will be recorded 

    When you submit the form an invisible reCAPTCHA check will be performed.
    You must follow the Privacy Policy and Google Terms of use.