Roast my custom tick database! (Database Administrators pls help)

*** See my comment made on 5/28/2025 for the current state! ***

For the technically minded/nerdy climbers among us, I'd love it if you could roast my database design!

I wanted to design a tick database to serve my own ends better than the MP profile ticks do, and I think I've arrived at a version I'm pretty happy with!

Please take a peek and let me know if you think there's something missing or something I could do better. None of my close climber pals are interested in databases and I don't have any database pals :|

Here's my github repo for the whole project complete with an idempotent SQLite3 script, training data, full(as much as memory can be) data from my 6 years of climbing and 1 year of guiding!

Are you sourcing the grades from MP? If so, something I've noticed is that if the MP grading is updated, then the grading of my ticks are not updated. i.e. Off With Your Feet in my ticks is an 11c but it's been upgraded to 12a again in the app. Just something to consider

Nate P wrote:

Are you sourcing the grades from MP? If so, something I've noticed is that if the MP grading is updated, then the grading of my ticks are not updated. i.e. Off With Your Feet in my ticks is an 11c but it's been upgraded to 12a again in the app. Just something to consider

That's a good note!
I'm only sort of sourcing the grades from MP. I downloaded the CSV of my ticks and cleaned the data to import into my own database, and now I'm just going to move entirely away from MP and only update my DB manually as I climb things. So they won't update if the MP consensus upgrades or downgrades something, but I'm not really splitting hairs over that kind of thing :)

This got me thinking about different ways people use the tick notes feature on MP.  It would be cool if some of this information could be structured instead of just in a text area. Some ideas:

• Who did you climb with (I see you already have this in your ticks.csv in your gh repo)
• Rack beta, move beta, approach/descent beta
• LRS / TRS distinction
• car to car time
• "Asterisk ascents" (ie french free, did not finish)

I also think you could include a separate table table called "Pitch".  This would allow you to record which pitches you lead on a multi-pitch climb, instead of having to write something like "Led P1, P3, P3", or indicate if you fell on a given pitch.  It could also be used to record if you linked pitches together.  If you include pitch height, your tick would have the total vertical feet of climbing recorded if you bail before the end of the climb.

I would use ID's as a key in pretty much all of your tables.  For example, multiple climbs could have the same name in the same area, think 'Redacted' or 'Unknown'.  

Lastly, just climbed at Seneca and had an amazing time :)

Ray Murphy wrote:

This got me thinking about different ways people use the tick notes feature on MP.  It would be cool if some of this information could be structured instead of just in a text area. Some ideas:

• Who did you climb with (I see you already have this in your ticks.csv in your gh repo)
• Rack beta, move beta, approach/descent beta
• LRS / TRS distinction
• car to car time
• "Asterisk ascents" (ie french free, did not finish)

I also think you could include a separate table table called "Pitch".  This would allow you to record which pitches you lead on a multi-pitch climb, instead of having to write something like "Led P1, P3, P3", or indicate if you fell on a given pitch.  It could also be used to record if you linked pitches together.  If you include pitch height, your tick would have the total vertical feet of climbing recorded if you bail before the end of the climb.

I would use ID's as a key in pretty much all of your tables.  For example, multiple climbs could have the same name in the same area, think 'Redacted' or 'Unknown'.  

Lastly, just climbed at Seneca and had an amazing time :)

Ooh, glad you had fun at Seneca!! It's underrated as hell, and surely I'm not biased hahaha

Those are some good points about the extra information that could be added! I think the extra ones I included reflect what I value in my data, but if anyone were to copy my project it would be pretty easy to tweak it to add their own fields to record things they care about that I'm not too fussed over!

Re: Pitch table. I included the height attribute under the Ticks table rather than the Climbs table for the very reason of bailing from a climb or doing something LRS where you climb it twice. This way with every tick I just guesstimate how many feet I actually climbed and record it in the tick. It adds a little extra work (I guide Old Man's a lot and the height wouldn't auto-fill from the climbs table, for example), but I think that's a detriment I can take in order to have the extra flexibility.

That's a great point about the ID's. Issues with my current primary key setup would be fringe cases, but they're probably out there somewhere!

Thanks for your thoughts! :)

I'm back! I've been busily working away on this database and holy camoly it really took off. I've started using it to tick my climbs, which made several things I missed quite clear, so I've steadily been fixing all the little issues that cropped up.

The diagrams below show the current state of things. I still want to add the ability to have climbs with different types of grades (5.7 C2, for example), but that's for later.

My current issue is mostly because I have no professional experience with this whatsoever and I don't really know what I'm doing. Right now I have a CSV for every table that I update whenever I tick new climbs, then I compile the database from the CSVs. I know this is dumb but I think it's less dumb than doing INSERT statements for everything. 

How do those in the biz update their databases?? I'd like to be able to update it from my android phone while offline at the crag, then have the database update when I'm back online. Right now my plan is to make an android app in kotlin to update a local copy of the database on my phone by filling out a form for a new tick, which then syncs to the copy on my server when I get back online. Is there a better way than that? I have even less experience with android app development than I do with database administration so I really don't know if that's the best solution.

Thanks a bunch!

(The github repo is fully up to date if y'all want to play around with it)

>. I know this is dumb but I think it's less dumb than doing INSERT statements for everything.
you should be able to generate all the insert statements based on some form fields. you'll need to do this to support your andriod app either way.

form fields -> some code to make insert statements (perhaps via an orm or just yolo some string templates and learn about sql injection someday) -> run the inserts

> I'd like to be able to update it from my android phone while offline at the crag, then have the database update when I'm back online. Right now my plan is to make an android app in kotlin to update a local copy of the database on my phone by filling out a form for a new tick, which then syncs to the copy on my server when I get back online. Is there a better way than that? I have even less experience with android app development than I do with database administration so I really don't know if that's the best solution.

you're looking for a sync engine. you can roll your own for the fun of it. make a flow to collect the form values you need. put them in json or something like that. if you have network access send them over the wire to your server and update the db. if you dont have network access, write them to sqlite or a folder. anytime you start the app see if there is network connectivity, and fire off the json you have laying around (then just delete them once you confirm they update)

edit to add: id be happy to help you out if you get stuck with anything.