workingSpecifically, 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?
