![[personal profile]](https://www.dreamwidth.org/img/silk/identity/user.png){kind=small}
Database back in sync :)
Jun. 29th, 2025 09:17 amYesterday and today I spent a couple of hours getting my relational database back into sync. Somehow, I was 5 kudos off -- but it wasn't as simple as just missing a single day when 5 guests had left kudos on a single work. Oh, no. There were 14 different works that were slightly off -- one too many here, one too few there. Some of the discrepancies were in guest kudos counts, but others were in named kudos-leavers.
So I laid it all out in a spreadsheet, then went through systematically and resolved the discrepancies. In some cases, I'm certain that I mistakenly attributed a kudos to work "45" when it should have been work "42" -- those numbers matched up exactly. In other cases it was a bit more involved to untangle what the kudos log should have in it.
One interesting thing was that as I went through the list of kudos-leavers on a few of these works, I found some renames. That is, I had them logged as one username back when they left their kudos, but now their username is different. (I know it's the same user because of where it sits in the chronological list of kudos-leavers on my fic.) So I performed several renames in my database, although of course I'll never be able to get them all. It's very possible that someone could leave a kudos, I could log it, then they could change their name and leave another kudos on a different work, and I could log that too, thinking it's a different user. I would never discover the discrepancy unless I happened to be comparing the list of kudos-leavers against my database, which I'm not going to do for over 7K kudos, haha! So me catching those and correcting them will probably be the exception, not the rule. Then again, most people don't change their usernames.
Anyway! No one cares about this but me, but I'm writing it down anyway because I love my database. I'm regularly dumping all four tables to Excel and saving them to cloud storage, along with my SQL queries, in case something awful happens to this laptop and I lose it. That way, I could still reconstruct the database on another platform.
I would venture to guess that I'm one of the only people in the world who has built a relational database just to track her AO3 kudos and wordcounts. That makes me a giant in my field! (a giant nerd, and proud of it!)
So I laid it all out in a spreadsheet, then went through systematically and resolved the discrepancies. In some cases, I'm certain that I mistakenly attributed a kudos to work "45" when it should have been work "42" -- those numbers matched up exactly. In other cases it was a bit more involved to untangle what the kudos log should have in it.
One interesting thing was that as I went through the list of kudos-leavers on a few of these works, I found some renames. That is, I had them logged as one username back when they left their kudos, but now their username is different. (I know it's the same user because of where it sits in the chronological list of kudos-leavers on my fic.) So I performed several renames in my database, although of course I'll never be able to get them all. It's very possible that someone could leave a kudos, I could log it, then they could change their name and leave another kudos on a different work, and I could log that too, thinking it's a different user. I would never discover the discrepancy unless I happened to be comparing the list of kudos-leavers against my database, which I'm not going to do for over 7K kudos, haha! So me catching those and correcting them will probably be the exception, not the rule. Then again, most people don't change their usernames.
Anyway! No one cares about this but me, but I'm writing it down anyway because I love my database. I'm regularly dumping all four tables to Excel and saving them to cloud storage, along with my SQL queries, in case something awful happens to this laptop and I lose it. That way, I could still reconstruct the database on another platform.
I would venture to guess that I'm one of the only people in the world who has built a relational database just to track her AO3 kudos and wordcounts. That makes me a giant in my field! (a giant nerd, and proud of it!)
