You're absolutely right in a perfectly relational sense. If City is dependent on State and State is dependent on Country, then there's no need to store StateID or CountryID in CustomerT once you have CityID. You can derive them through the relationships.

ZIP codes aren't always a perfect relationship, though. A ZIP code can span multiple cities, and a city can have multiple ZIP codes. I built a database for one of the TechHelp lessons where you enter a ZIP code and it returns the possible cities for that ZIP code.

This was just a quick, simple example for the class. A more natural real-world example might be Department, then Category, then Product. Once you store ProductID, you don't need to also store the DepartmentID and CategoryID because those can be derived from the product relationship.

Of course. In a fully normalized design, you would normally store only the lowest-level foreign key and derive the higher-level values through the relationships.

There are cases where you might intentionally store those additional foreign keys, though, particularly if you have a very large table and frequently run reports grouped or filtered by something like Country. Keeping CountryID in the record can avoid extra joins and make those reports simpler or faster. That's called denormalizing the database.

It's not something you need to do very often, and it does mean you have to make sure the redundant values stay consistent. But it's a valid practical tradeoff when the performance or reporting benefit justifies it.

Ha ha, don't get me started for when the client says, "But we keep that screwdriver in the hardware and automotive departments." And I ask, "How am I supposed to know which department the customer took the screwdriver from when it's at the checkout register?"

Exactly. The database knows where the inventory is assigned or stocked, but unless you track the specific bin, department, or fulfillment location as part of the sale, you can't magically know which physical screwdriver the customer grabbed at checkout.

That's where real-world business rules start complicating an otherwise nice, clean relational model.