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:
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:
A story with the full set of Portkey.org pairings (H/Hr, R/L, D/G (yuck), JP/LP) would have these pairings in the table:
And that rather scary Harry/Harem thing I mentioned earlier:
That actually would work, but it's a little messy and confusing. Is there a better way?
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)
- 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)
- 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))
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)
| id | type | fandom | name |
|---|---|---|---|
| 1 | character | 42 | Granger, Hermione |
| 2 | character | 42 | Lovegood, Luna |
| 3 | character | 42 | Malfoy, Draco |
| 4 | character | 42 | Potter, Harry |
| 5 | character | 42 | Potter, James |
| 6 | character | 42 | Potter, Lily |
| 7 | character | 42 | Weasley, Ginevra |
| 8 | character | 42 | Weasley, Ronald |
| 9 | character | 42 | Patil, Padma |
| 10 | character | 42 | Patil, Parvati |
A story with the full set of Portkey.org pairings (H/Hr, R/L, D/G (yuck), JP/LP) would have these pairings in the table:
| storyid | shipno | character |
|---|---|---|
| 24 | 1 | 1 (Hermione) |
| 24 | 1 | 4 (Harry) |
| 24 | 2 | 2 (Luna) |
| 24 | 2 | 8 (Ron) |
| 24 | 3 | 3 (Draco) |
| 24 | 3 | 7 (Ginny) |
| 24 | 4 | 5 (James) |
| 24 | 4 | 6 (Lily) |
And that rather scary Harry/Harem thing I mentioned earlier:
| storyid | shipno | character |
|---|---|---|
| 25 | 1 | 1 (Hermione) |
| 25 | 1 | 4 (Harry) |
| 25 | 1 | 8 (Ginny) |
| 25 | 1 | 9 (Padma) |
| 25 | 1 | 10 (Parvati) |
That actually would work, but it's a little messy and confusing. Is there a better way?
- Current Location:cushy red chair in the library
- Current Mood:
working - Current Music:Pink Floyd - The Wall (Disk 1)
Profile
brentdax- Brent Dax
- Architechies Touch Software
Comments
Though, with any pairing code, is it going to distinguish between, for example, Harry/Hermione and Hermione/Harry? Not all fandoms (and not all authors within a fandom) make a distinction between that sort of thing, but it's still something to consider.
No. Where possible, the system tries to find "canonical" forms for things, so that we don't end up in a hell where there are stories marked H/H, H/Hr, Hr/H, Harry/Hermione, Hermione/Harry, etc. That's why I'm not going with a straight "type in your tags" system like LiveJournal has, except for custom and user tags—the genres and so on get bunches of checkboxes, the pairings get rather nifty multiple-drop-down-menu thingies. (Basically, you select "Potter, Harry" and it reloads a version of the form with another drop down menu to the right of that one; select "Granger, Hermione" and it gives you another menu; select "Weasley, Ginevra" etc.)
To get the names of all the tags on a fic, I just have to do:
SELECT tags.name FROM tags, storytags WHERE storytags.storyid = (blah) AND storytags.tagid = tags.idSimilarly, getting all the data I need to construct a pairing list is a single SQL statement:
SELECT tags.name, storyships.shipno FROM tags, storyships WHERE storyships.storyid = (blah) AND storyships.character = tags.id(After that, I just have to do a little Perl trickery to get an array-of-arrays representing the ships. I've written a module that has a function which should handle this nicely.)
On the other hand, what you're talking about means a SQL statement, some Perl, and a bunch more SQL statements:
for(SELECT tags.name FROM tags, storytags WHERE storytags.storyid = (blah) AND storytags.tagid = tags.id AND tags.type = 'ship') { @charids = split '.', tags.name for(@charids) { SELECT name FROM tags WHERE id = (blah) } }Hmm...perhaps I'm approaching this wrong. What if I create tags for each pairing as needed, and use a secondary table to keep track of which characters are in the pairing? For example:The tags table has this:
The ships table, which is only used to figure out which ships already have tags, has this:
H/Hr stories are just marked as having tag #11, which just happens to be a ship tag. When someone uploads an H/Hr story, the software checks to see if there's already a tag for H/Hr, and sees that tag #11 already exists for that, so it uses that. But when someone goes nuts and tries to upload H/Hr/G/Pv/Pd, it sees that such a thing doesn't exist, so it adds a new tag:
And then notes it in the ships table with new entries:
The win: The only special thing about a ship is how it's created; in every other respect, a pairing is just a normal tag.
The loss: I was hoping that, except for "custom" and "user", we could avoid having tags which were created by normal users (as opposed to administrators). In crossover fics, cross-fiction pairings become impossible, because a tag can only be associated with either one fandom or all fandoms. (But maybe this isn't so important, as ship tags aren't added using the same menus as other kinds of tags—they're constructed out of drop-down lists.)
Oh, though here's another thought in general- maybe have an optional spot for people to mention what spoilers their fic might give for a series?
Possible variations on that, of course, such as having a list of suggestions viewable to the author, but if enough people suggest the same thing it gets added automatically, etc.