In case you missed it:
I'll let you in on a little secret: I have a superpower.
My superpower is that I overcomplicate things. It's insanely unproductive, but it leads to all kinds of lessons that I never would have learned otherwise.
So while Part 1 and Part 2 were interesting, consider them broken eggs. You gotta break some eggs if you want to make an omelette, right??
Anyway, shout out to Tom who commented on Part 1 (Tom's a data analyst in his day job) who gave me some unsolicited advice that was much appreciated: it's better to have more smaller, “tall and narrow” tables in your database than one big one because you can link everything together.
I devoured a couple of database management “philosophy” resources last night and realized that I wasn't building a solid foundation.
The ebook versioning stuff was money, but the database design itself wasn't solid.
So I blew EVERYTHING UP AND STARTED FROM SCRATCH.
Setback? At first, yes. But after the end of the day? Not at all. Huge leap forward (for now).
The Brains of the Database: My Books
My “mission statement” of this potential database might look like this:
- To be able to enter and track every book I've ever created
- To track WHEN I updated/rebranded books, and WHAT I updated
- To track formats for each of my books for copyright purposes (ie. what's in paperback, what's in audio, and what's not)
- To track sales for each of my books
The goal is to build a database that will is flexible, scalable, and easy to maintain for at least 30 years.
The heart of the database, ie. the part that will let me do everything I listed above, is my BOOKS.
So I started from scratch and created smaller tables.
The BOOKS table merely lists my books and a few key attributes.
Since I write many different kinds of books, I decided it was better to have all my books in ONE table and have a dropdown that determines WHAT they are.
(NOTE: I only entered my 2014 books for this exercise. I don't like re-entering data down the road, so I'd rather get things right with a handful of books and then I can expand later).
I also learned about primary and foreign keys (which I won't go into here), but essentially I don't have to LIST every single attribute on every table. I can simply use an ID to reference it.
So what I did was create a separate table for AUTHOR and SERIES.
Sounds really weird, right?
But it prevents me from having to enter in the same values over and over.
The author table looks like this:
The great thing about this is that for books where I have a co-author, I can simply use an author ID for that versus typing it in.
Here's what the series table looks like:
And really cool part is that I can expand each series and see the reading order. How cool is that?
I can also do the same thing with genres, which I admit is a tad bit wonky, but it works.
Anyway, that's the book table.
I also tried add formats for the books, but I did it differently. I created separate tables for paperback, audiobook, and translation editions.
Paperback for example looks like this:
The rationale here is that paperback data is more similar to itself than it is to anything that's on the book tab. So I basically linked the paperback table to the book table by including the book ID.
Same with audio and translation.
Running Some Queries
Yesterday, I tried running some queries but they were a complete and utter disaster. All my fault.
I'm not sure if it's because I'm more comfortable running queries now or the databases are better built, but all of my queries today were a breeze.
Example #1: Let's say I want to know which of my novels are in paperback format. Early in 2014 and 2015, I didn't do paperbacks because Vellum didn't exist and I hated trying to mess with Microsoft Word.
Here's how I set up the query:
The query returned this result, which is correct.
Example #2: Let's say I want to know which of my books are in ebook, paperback, AND audio, and their release dates. Here's how I set up the query:
The database correctly returned the following books:
Example #3: How many words did I write in 2014? Easy.
Half a million words in 2014–MY FIRST YEAR OF PUBLISHING? WHOA!!!
But that's not correct.
For two of my first novels, I had these silly “International Editions”. Long story…but I need to remove them. With a bit of filtering and sorting, the final word count for 2014 was 301,000 which is not too shabby!
I'm still learning how to do more advanced queries so I know that these are dead simple.
But imagine building a database where you can ask it virtually any question and get an accurate answer in seconds.
That's my north star, and it's insanely powerful. You can't do that with Excel. There's no spreadsheet big enough to let you.
I've still got a lot to learn, a long way to go, but this isn't too shabby for a guy who knew nothing about building databases two days ago.
I haven't even started messing with front-end design. I'm doing things the hard way right now so I can learn how everything works behind the scenes. When I'm done, I hope this will look a lot nicer.
Anyway, that's it for tonight. Thanks for reading this. And don't forget to join my Fan Club, which is a pretty fun place to be these days!
