Database catchup
Feb. 28th, 2025 09:38 am![[personal profile]](https://www.dreamwidth.org/img/silk/identity/user.png){kind=small}
artemisdartI had left things for over a month, but last night I finally sat down and did all the data work to catch up my relational database to real life. My kudos count now matches EXACTLY!!!
Seriously, it's really thrilling to query SELECT SUM("Kudos_count") FROM "Fact_Kudos"; and have the answer MATCH what AO3's webpage says it is! :)
I also caught up on entering the flurry of new works and chapters that I created recently while I was trying to get to 100 works by 2/25. The wordcount doesn't quite match there, because the announcement emails with the wordcounts in them are one thing, but sometimes I edit after the fact and then the words don't match for multi-chapter works (AO3 doesn't show wordcounts by chapter, only overall). So for those cases, I need to adjust my "Word Count Adjustment Chapter" entries for each affected work, and I haven't done that yet.
It's not super important, so I'm not too bothered about it.
Recently I added a couple of new columns to my Dim_Work table: Work_reveal_date, to go with Work_create_date; Work_blogged_about (a BOOLEAN column to show whether I've posted a "story blurb" entry about it here or not); and Work_rating (Gen, Teen, Mature, or Explicit).
I've been pondering creating a Fandom table and linking each work to that through a key, but I'd need a bridge table to handle works with more than one fandom, and so far I haven't felt like doing the work.
Same situation for creating a Ship table and linking each work to that through a key. I'd need a bridge table because one work can have more than one ship.
For now, the database does what I want. It allows me to chart my kudos and wordcounts posted (and now, wordcounts revealed!) over time, in ways that AO3 does not. Plus I can easily track users who have kudosed more than one work. My top kudos-er has left a kudos on 48 separate works of mine, and my #2 fan has left works on 23! Wow, I should send those guys a fruit basket or write them a cute little one-shot or something! :)
Seriously, it's really thrilling to query SELECT SUM("Kudos_count") FROM "Fact_Kudos"; and have the answer MATCH what AO3's webpage says it is! :)
I also caught up on entering the flurry of new works and chapters that I created recently while I was trying to get to 100 works by 2/25. The wordcount doesn't quite match there, because the announcement emails with the wordcounts in them are one thing, but sometimes I edit after the fact and then the words don't match for multi-chapter works (AO3 doesn't show wordcounts by chapter, only overall). So for those cases, I need to adjust my "Word Count Adjustment Chapter" entries for each affected work, and I haven't done that yet.
It's not super important, so I'm not too bothered about it.
Recently I added a couple of new columns to my Dim_Work table: Work_reveal_date, to go with Work_create_date; Work_blogged_about (a BOOLEAN column to show whether I've posted a "story blurb" entry about it here or not); and Work_rating (Gen, Teen, Mature, or Explicit).
I've been pondering creating a Fandom table and linking each work to that through a key, but I'd need a bridge table to handle works with more than one fandom, and so far I haven't felt like doing the work.
Same situation for creating a Ship table and linking each work to that through a key. I'd need a bridge table because one work can have more than one ship.
For now, the database does what I want. It allows me to chart my kudos and wordcounts posted (and now, wordcounts revealed!) over time, in ways that AO3 does not. Plus I can easily track users who have kudosed more than one work. My top kudos-er has left a kudos on 48 separate works of mine, and my #2 fan has left works on 23! Wow, I should send those guys a fruit basket or write them a cute little one-shot or something! :)
