I think the answer here is at least very close to, if not the same as, the answer to "how close to a particular ideal should we strive for to reach this goal?" Different steps along the way in the development life cycle, different disciplines (within software development: application programming, database design/programming, UI, QA, etc) have various ideals. Must all projects have n-tier approaches? Is it ever forgivable to put application logic in the database? Can it even be suggested to use a cursor (I said that quietly so that nobody would hear it ;-)? The question comes down to:
- Is the current state of the task at hand leave the project with something that works and can be maintained? Or is it the bare minimum to make it appear as if things are working?
- What is the gain for the extra time / effort required to move closer to the ideal?
If the current state is either barely functional and/or barely maintainable, then the second question is easier to answer: the gain for moving closer to the ideal is a system that works (less wasted time in the future, constantly coming back to fix things) and is maintainable (less wasted tie in the future when you either have to fix something or requirements change and new functionality is requested).
The main difficulty in answering these questions, though, is that it requires experience. It requires having worked on several projects, having tried different approaches to problems, and seeing what worked in which situations and didn't in other or maybe wouldn't really ever be a workable idea.
With the two example models given, the one with the repeating fields (i.e. less normalized) is functional, but over time will show itself to be harder to maintain and harder to adapt to changes necessitated by new requirements. I know this from experience as I have had to work on system where this approach was taken. Not only did it cause problems and was harder to debug (people manually entering those string values over time makes for less consistent / predictable data than was ever imagined possible by anyone choosing to go down that road at design time), but it also slowed queries down quite a bit as filtering on
text fields isn't as fast as filtering on
But experience is also required to know if you have gone too far. Or maybe gone the appropriate distance but in a slightly inappropriate direction. Take the first model shown, for example. There are two modeling mistakes:
The "university_major" table should not have "program_id" in it. "program" should relate to "major" and can be inferred from "university_major" through its relationship to "major".
The "university_major" table should not have the "university_major_id" field. It is a redundant key that adds no value due to being a surrogate, auto-incrementing value. There is a natural key available to be the PK: "university_id", "major_id". The arbitrary nature of "university_major_id" would allow for multiple instances of "university_id" and "major_id" unless an additional Unique Index or Constraint is added for those two fields.
And again, it is mainly experience that lead me to those thoughts. I also realize that there are situations when a system needs a single auto-incrementing value (I believe replication in SQL Server requires it on all replicated tables), and so you go with an IDENTITY field for the PK and add the UNIQUE INDEX and move on with life ;-).
So I would say there is no actual, measurable way to know when to stop normalizing. It mainly comes down to experience. I would also add that collaboration with others (to make use of their experience) and assessing the current project (allotted time and resources, target audience, etc) play a role. But I would also argue that someone's willingness and/or ability to recognize / value / make use of those other factors is, at least partially, a proportional to their level of experience.