[FIXED] How to design tables to support has many relation with default value?

Issue

How to design tables for supporting has many relation with default value?

For example:
Country has many cities. One city is the default for the country.

Tables design option 1:
City table will has the boolean field - is_default.

Tables design option 2:
Country table will has the foreign key field - default_city.

Queries that will be written:
Update and fetch default city and change which city is the default.

Which option is better and why?

Thank in advance.

Solution

There are philosophical aspects, as well as practical aspects to this question.

The philosophical question is "which entity should know about the default status - country or city?". Can a country exist without a default city? Is "defaultness" an attribute you can associate with a city? Are there any other attributes which might apply to the relationship between city and country?

The philosophical question matters because in the future, someone looking at this schema without being part of the initial conversations should be able to understand the intent without having to read the documentation.

I don't know the answers, by the way, but it's worth having this conversation with your team - I believe the default solution should be "do what matches the domain model best".

Then, there are some practical concerns.

As @SaadAhmad writes, if "default city" as an attribute of country, and you want to make it mandatory, the city record must exist before you create the country and that is impossible because "country" is a mandatory field for "city".

The other practical question is "how do I enforce the business rule that a country must have 1 and only 1 default city"? That's easy by creating "default city" as not-null field on country, but hard to do with "is_default" being an attribute of city - a unique index on "country_id, is_default" means there can be only one default city per country, but doesn't enforce the rule that there must be at least 1.

So, the answer is:

• figure out what the business domain is telling you first.
• making "is_default" an attribute of city is practically the easiest because you don't have to worry about cities existing when creating the country.
• you have to deal with the business rule requiring there to be exactly one default city for each country

Answered By - Neville Kuyt

Answer Checked By - Robin (PHPFixing Admin)