sql server - Table creation - move common fields to separate table

Question

I have a scenario that, there are three types of functionalities has same set of fields (except their primary key).

The below is the sample. I would like know, whether it is a better idea to group the common fields in a single table? If we create a common table, how can we give the FK reference to the corresponding primary key table? What would be the better approach?

tblCountry	tblState	tblCity
countryid	StateId	CityId
Name	CountryId	StateId
officiallanguage	officiallanguage	officiallanguage
officialFlag	officialFlag	officialFlag
officialFlower	officialFlower	officialFlower
officialAnimal	officialAnimal	officialAnimal
officialBird	officialBird	officialBird
...	...	...
...	...	...
...	...	...
etc	etc	etc

question from:https://stackoverflow.com/questions/65841628/table-creation-move-common-fields-to-separate-table

Answer 1

Your intended third normal form (3NF) is good as it is. From a simplicity point of view (affecting joins) it is as good as it can be. And foreign keys between country, state and city are obivous and trivial.

Now to save you from copying some column names you could put all three elements country, state and city into a single table - effectively making it second normal form. With this the meaning of your column names start to roleplay. With this I mean the officialLanguage can either be country, state or city related. From the stored table design this is no longer obvious. Only by interpretation of the multi column key.

So in short by saving on some typing / copying you will complicate any further work using a single table with convoluted meaning instead of using three tables with clear meanings.

Now towards data selection this is an issue only if there are no aliases. Consider selecting officialLanguage of a city in a country.

SELECT
  name,
  officialLanguage,
  name,
  officialLanguage
FROM city
  INNER JOIN state
    ON  state.stateid = city.stateid
  INNER JOIN country
    ON  country.countryid = state.countryid
;

This will fail as the columns chosen are ambigiuous.

Now consider this query (where the aliases are shorted just to demonstrate the aliases - personally I try to use up-to-10-letters aliases):

SELECT
  cit.name AS city_name,
  cit.officialLanguage AS city_language,
  cou.name AS country_name,
  cou.officialLanguage aS country_language
FROM city AS cit
  INNER JOIN state AS sta
    ON  sta.stateid = cit.stateid
  INNER JOIN country AS cou
    ON  cou.countryid = sta.countryid
;

It is very clear and concise. I can use country table in different queries without having to pre-select those countries from a table with intermingled objects like country, state and city.

The only downside to this approach is the multi join of properly indexed tables.

Also as there are quite a few countries, states and cities across the world this single table approach can be a performance issue down the line.

4NF (or at least BCNF or 3.5NF as it is otherwise known) is best for fast performance in joins with the trade off that joins can become complex (to write) when properly indexed. However for database engines these are easiest to read.

2NF (or Excel tables as I call those) are easiest to read for humans. Which require complicated join and/or conditions (WHERE clause) to properly identify just a subset.

For the database design best use at minimum 3NF or better, then prepare views to turn the data back to 2NF to make your data human-readable.