Should I create a new table or save "static" data in all the tables? [closed]

Question

I'm using Postgres as the database management system.

Currently, I have a countries table, which has only four countries, i.e. four rows (might add one or two more countries, i.e. rows, later).

--Table 1: countries

id
country_name

I have many tables, referencing to this countries table.

--Table 2: events 
name
country_id
date

--Table 3: servers
server_name
country_id


--Table 4: packages
package_name
country_id

The problem is whenever I want a country name, I wanted to join the countries table with other table and make the query to get the country_name value(s).

As the countries table is "static" with only four country rows, should I create a column named country_name in the tables 2, 3, 4 and save the country value as a string, as this will get rid of the countries table and also will get rid of all Joins in my query?

Kindly advice what is the correct approach when dealing with "static" names.

Answer 1

There is another design you could consider. country_name would be considered the natural key for table countries, but it is not the only natural key. Every country has unique codes defined by ISO. These values are as fixed as your country_id would be. They have the advantage of being somewhat human-readable, however. For example the code for the United States of America is "USA".

You could use one of these ISO codes as the key of countries. The ISO code will also be the foreign key in your other tables. Reports and queries will return the ISO codes and humans can read them directly. Only when the country's full name is required will a join be written.

This is discussed further at this question.

I would not duplicate non-key columns across referencing tables just for the sake of it. The opportunities for inconsistencies is just to great. The overhead of an additional join to a four-row table is slight. If, in the future, you achieve great scale and it can be shown categorically that this join is the bottleneck then I would consider denormalizing, having put robust code testing and reconciliation procedure in place.

Answer 2

The correct answer totally depends on the wider system you are building, how it will be used and the potential for growth.

You currently have it in a normalised form which will give you many benefits versus storing the data per table.

• It reduces database size. If you stored that same data in each table you have three times as much to store. Indexes will also multiply this number and it all adds up.
• You'll also be helping SQL Server as it will be able to cache data and plans related to the shared table rather than having to use more RAM to cache the same data multiple times.
• It makes database growth easier. When you do add more countries you would have to add them for each and every new table using the static method.
• Updates are easier. If you made a spelling mistake you only have to update one location.
• Data integrity is easier to enforce. You know that table only should have countries in it so you and you only want tables that reference it to use values contained within so you could add foreign keys to help enforce those relationships.
• Data reuse. As a single table for country data you may want to use it in future developments. You wouldn't have to recreate it each and every time.

Because you never know how it will be developed further, plan for the future and build it to be scalable so keep it as it is.

Answer 3

I would say the database design (normalzation or not) for your lookup table (countries) depends a) on the amount of data you have/expect in the tables events, servers and packages AND b) on the queries you expect

Notes on a): If you expect millions of entries, you can think of a denormalization, i.e. just save the country as string as you proposed. You have also other options, as pointed out in https://stackoverflow.com/q/21184352/8957103

Further Questions and notes on b) Do you plan to query e.g. events with the country_id or the country_name? Do you plan to query it often (and thus have a index on it)? If you query with the country_id (often) you don't need a join and the separate table does not matter. If you want to use the country_name, a separate table may make sence in term of disk space and normalization (also database constraints/foreign keys can be used), but for querying often I would not recommend that.