Jump to content
  • entries
  • comments
  • views

Back End - Database



So, let's learn some more details about exactly what I'm gathering and how I'm structuring it. First, we'll start with the actual tables and relationships that I used to construct the database. (A Microsoft Access database is, at its core, a collection of tables. Lines from the different tables can be linked together, to reflect connections among those tables. Microsoft actually provides some pretty good training videos, if you don't know anything about Access. )

So, the centerpiece of the database is the WoB Table, which stores the quote itself. It also holds several other pieces of information, namely the type (Reddit, Twitter, Signing, Interview, and Signed Book are the five Types I have now), the date, and the hyperlink. (For some, but not all. More on that later.) Here's an image of the table, split into two parts because it's wide.


WoB Table 1.PNG


WoB Table 2.PNG

So, you can see it's a lot like Microsoft Excel. A big difference is that there are some check boxes, a data type that I'll be able to use for a couple of all-purpose filters.

  • RAFO - marked if a question is only answered with a RAFO. Still want to document it, but want the ability to exclude them from searches.
  • Conflux - this is more of a personal filter. I don't particularly like coming across all these "what if you Awakened a fabrial" questions, so I want the ability to filter them out. This might not actually go anywhere, but it's easier to build in from the get-go, rather than try and add it after the fact.
  • Paraphrase - mark if the text of a WoB is paraphrased
  • Juicy? - again, more of a personal filter, for quotes that I feel everyone should be aware of. I'll probably call these "Notable" WoBs when I get a better interface, but I'm a little afraid to try and rename columns at this moment.
  • Archive - there are a lot of old questions that were great when they were asked, but aren't so important right now. I won't want to delete these going forwards, but I would like a way to mark them.

The other data fields you see are mostly about sources. There are two fields (date, hyperlink) that each entry has. But, wouldn't that get tedious, if I'm adding from a signing, where there are 100 questions that all have the same date/time? It certainly would be, and that's what the Event field is for. When I click on that field, it actually brings up a drop-down list of another table: the Sources Table.


Sources Table.PNG

This table has a line for each signing, its date, and the hyperlink to the forum link for that signing. (And a couple of housekeeping fields I'm using to keep track of things.) By connecting a WoB to a Tour, I can avoid repetitious data entry, and also have a nice way to identify all WoBs from a particular event.

The last field I haven't talked about in the WoB table is "Keywords." You may have noticed it looks a little empty, with good reason. It's just a catch-all field for stuff that doesn't fit in any of the other seven tables.

That's right, there are seven other tables.

But these tables are pretty simple; they're really just lists. They only have one column apiece. Here's the Character Table.


Character Table.PNG

So, not too bad, it's just a catalogue of characters who I'd like to use as tags for WoBs. (I'm adding as I go, so there are still a lot of characters missing.) But these connections are not as simple as the sources; I can't just use a single column in the WoB table, since you can have multiple people for each WoB. This is taken care of with a Junction Table for Characters and WoBs, which categorizes every single connection that can be made between WoBs and Characters.


WoB-Character Cross Reference.PNG

So, this table shows that WoB 1 is just about Kelsier, WoB 2 and 3 aren't about any particular characters, and WoB 4 is about both Kelsier and Marsh.

This lets me assign a whole mess of tags to any given WoB, while maintaining a structure and not leaving the Keywords field super cluttered up. It also lets me ensure that I'm being consistent with tagging - am I can't tag Wax as Waxilium, 'cause it's not in the table.

So, that's one category of tags I'm using to divide up the WoBs. The other six are: Groups (like organizations or countries), Magic (systems and other manifestations), Realmatics (for topics about the underlying philosophy of magic), Planets (for worldbuilding and history), Series (a broad category, to pull together, say, all the Stormlight WoBs; includes Crossovers as an option), and Shards (which includes Adonalsium). The Keywords field from the WoB table isn't structured the same way; it's just a field you can type whatever you want into. I'm thinking about making a Miscellaneous category, for stuff like Future Plans and Languages, but for now I'll just leave it as it is. (Incidentally, the Groups and Realmatics were late additions; I had been adding stuff like Cognitive Shadows and Worldhoppers as Keywords, but there were a lot of them, so I decided they needed their own structure.)

Each of these tables has its own Junction table. And, yes, that turns into a lot of tables. Here's the total arrangement of all relationships and tables in the database:



In the center, you see the main WoB table. Around it are the junction tables (which will connect a particular WoB with specific items in the tag tables), and then the tag tables themselves. The lines represent relationships, where the values on the "1" side are used for the cells on the "infinity" side.

If you're anything like me, you start seeing all these other connections between tag tables. I can say which Magic systems come from which worlds and which Shards, say which Characters appear in each series, etc. Which could be a fun exercise in database design... but, ultimately, they don't help me organize WoBs, so there's no good reason to spend time building them.

But, still, that works out to 16 tables to take and categorize the WoBs. It's a lot, but if you remember at the top, I called this the Back End. This is all the behind-the-scenes structure. Actually getting at the data looks very different, 'cause I don't want to have to tab through a dozen different tables for a single WoB, and this isn't intuitive for using. That's for the Front End, which I'll talk about in another post. And it looks a lot simpler, don't worry.


Recommended Comments

There are no comments to display.

  • Create New...