0

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.

closed as primarily opinion-based by hot2use, Marcello Miorelli, kevinsky, MDCCL, George.Palacios Jul 30 at 13:23

Many good questions generate some degree of opinion based on expert experience, but answers to this question will tend to be almost entirely based on opinions, rather than facts, references, or specific expertise. If this question can be reworded to fit the rules in the help center, please edit the question.

  • should I create a column named country_name in the tables 2,3,4 and save the country as string 1) The queries which aggregates data by country can become more expensive. 2) And how are you going to deal with typos? 3) If you want to have a string type, and the values list is small and static - why not ENUM? 4) Are another tables NOT joined between each other (directly or via another tables)? by any parameter, not exactly by country. – Akina Jul 24 at 7:57
  • As you are mentioning String is not an option here. Other tables are still joined by other columns. I just wanted to clear my doubt on this particular scenario whether should i create new table or as you were mentioning should I use Enum on all tables. – Srikanth Jeeva Jul 24 at 8:05
  • Other tables are still joined by other columns. That's very bad and can produce data inconsistency (a record from table A referred to country X is joined to a record from table B referred to country Y). You must look carefully on your structure and analyse the dependencies - somewhere the counry is direct property whereas somewhere else it is slave property transferred from another entity property. Slave properties must be removed from the table, their values must be obtained in the query via joined record's direct property. – Akina Jul 24 at 8:09
  • Welcome to DBA.SE and thanks for your participation. Does your countries table have a country_id column? I assume yes, but I'd prefer to hear it from you. Please edit your question and add the details. Thanks. – hot2use Jul 24 at 9:06
  • countries table have id as primary key and country_id will be foreign key in other tables. – Srikanth Jeeva Jul 24 at 18:27
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.

0

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.

0

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.

Not the answer you're looking for? Browse other questions tagged or ask your own question.