1👍
There are a few things wrong with option B, from my point of view. One is that there’s nothing to stop two or more cities from being marked as capital for a particular state or country. You’d need to add extra validation on your forms to ensure that didn’t happen.
From the point of querying, I think A is still preferable. For instance, if you wanted to get all cities in the state whose capital is City X, in the first one you would do:
cities = City.objects.filter(state__capital__name="City X")
and in the second you would have to do this:
cities = City.objects.filter(state=City.objects.get(name="City X"))
which resolves to a subquery, which is likely to be less efficient.
However, in option A I don’t think you necessarily need the FK from City to Country. In your model (*), all cities are in states, so you can always get the country via the state – having the extra FK again means you need more validation to ensure that you can’t assign a city to a country that’s different from its state’s country.
(* although note that this doesn’t actually match reality: not all countries have states anyway, and even those that do will often have cities that fall outside all states, like Canberra in Australia)
1👍
Option A is better because it contains the same information without requiring a set of fields that will almost always be blank (state_capital and country_capital).
As you note, not every country has states/provinces, so you should have both country and state fields for the City model. In any case, it is a very small amount of redundancy for a huge payoff, and you are unlikely to encounter normalization problems (for example, it’s extremely rare that a given state changes its country). Even if every city did have an associated state in your model, it’s still better to add the redundant field to speed up the query — you’re then dealing with at most one table join instead of two.
Note that Washington, DC is composed of two places: Washington the city and DC the state. So your query:
Get me a country where country is US. and capital is Washington DC.
Should read as
Country.objects.filter(code__iexact='US', capital__name__iexact='Washington', capital__state__code__iexact='DC')
(I’m assuming here that for both the Country and State models you’re actually adding a code field to deal with abbreviations)
I would note that some of these queries seem pretty redundant: Why would you need to match countries on both the country name and the capital?
- [Answered ]-Overriding __init__ for a form and setting the checkbox disabled and using initial=True
- [Answered ]-Django view method signature is it possible to match GET parameters?