It's hard and it doesn't scale -- at least not in the implementations I've seen.
The question think of on when looking at database design issues in Moodle (and general internal API design) is
- what real end user problem will we solve?
- what real programmer problem will we solve?
The only ones that I personally care are along the lines of
- it lets us build something we _really_ could not build before for users
- scales 10x better when you have 2 million users and 500k courses
- it scales better when you have a surge of 300 users hitting you in a 5 second window
- it'll make the code _simpler_ by a combination of less lines of code and less/simpler/safer abstractions to understand. it's trivial to reach less lines of code with increased abstraction, but then you have a bad tradeoff
- it will make things easier to change: if we later want to track more data (columns/tables/etc) we have to modify 1/3 of the places in the code to support that. And look! the upgrade block to do that only takes 3 lines, and it's failsafe and won't risk eating anyone's preexisting data.
It's _really easy_ to come up with theoretical improvements, or things that fit your taste in programming. It's also mostly a waste of time The problems that are worth tackling usually hit at least some of the items above -- and they are hard problems.
A good rule of thumb: the internals of any large project look "wrong" when you start working on it. Only after hacking on it for a while you start understanding why they are that way. There's a logic to the madness. This doesn't mean that there aren't things that really need changing (there's a TON!) -- but they arent the things that seem obvious at first...
(Having said that, we have some nasty legacies in there like the bad NULL handling that originates in olden MySQL limitations and is now giving Eloy nightmares with Oracle.)