True, but couldn't you pretty easily derive the logical ID via some sort of pagination scheme? That is, when you really want to see it at all. It seems like this would be a pretty low cost query as long as you set your indices up properly, and I'm assuming for the hundred-thousand backer projects you'll never need to display every backer in order.
Another slight concern is how big this project_backers table would get, since it's a combination. Let's assume every project has an average of 10k backers and there are about 100k projects. That makes 1B relationships, with O 128 bits per row (plus whatever associated data). 16 GB. Not too bad for such an important relationship. Even with 100x growth and no garbage collection or archiving, you could fit it on (a couple of) flash disks for the foreseeable future. I don't know if MySQL supports delta encoding but that would also probably make the table cheaper to store.
You could, but then you have multiple identifiers for the same object, and you end up having to decide "wait, which ID do I want here?", and during long nights when you're hopped up on five cans of Red Bull, the numbers start to run together, and you don't know if you're looking at a real ID or a logical ID, and even when you do know, you end up running queries directly against the database trying to convince yourself that the mapping can't possibly be correct.
Been there, done that, consumed a lifetime supply of Mountain Dew in a year, and handed in a multi-page resignation letter[1]. I'd rather use their solution, which ultimately minimizes the overall complexity of the system.
[1] OK, the resignation letter wasn't really about IDs, but the ID problems were a symptom of larger problems.
Interesting. I would expect that the logical id would be displayed only, never stored, but that would make debugging display issues difficult.
Thank you for your thoughts. It's helpful to hear the thoughts of other database professionals, especially in domains where I have no knowledge, like relational databases.
Another slight concern is how big this project_backers table would get, since it's a combination. Let's assume every project has an average of 10k backers and there are about 100k projects. That makes 1B relationships, with O 128 bits per row (plus whatever associated data). 16 GB. Not too bad for such an important relationship. Even with 100x growth and no garbage collection or archiving, you could fit it on (a couple of) flash disks for the foreseeable future. I don't know if MySQL supports delta encoding but that would also probably make the table cheaper to store.