What is the reason to "normalize your databases"?

Question

In the past, I've been told (on this site) that I should normalize the values in the database - using a lookup table instead of using direct (string) keys.

I am confused why this is so good that several people recommended this. Is it just for memory consumption? But then in my case (explained below) how much is that?

Consider I have a dictionary database for a website:

CREATE TABLE dictionary
(
    id serial NOT NULL,
    key text NOT NULL,
    language text NOT NULL,
    value text,
    PRIMARY KEY (id)
)

And then insertion would happen like:

INSERT INTO public.dictionary VALUES
    ('yes_button', 'en', 'yes'), ('yes_button', 'nl', 'ja')

Or instead of 'en' I'd use 'en-us'. Now I've been told to "normalize" the database - which would mean having a lookup table that binds the string representation of a language ('en', 'nl' to a value):

CREATE TABLE languages
(
    id serial NOT NULL,
    language text
)

CREATE TABLE dictionary
(
    id serial NOT NULL,
    key text NOT NULL,
    language integer NOT NULL,
    value text,
    PRIMARY KEY (id),
    FOREIGN KEY (language)
        REFERENCES  public.languages (id)
)

However, this would increase complexity quite a bit, since insertion can no longer be simple — it needs to either check the foreign table on the backend, or use some more complex SQL. So there is a real cost to updating to this design.

What are the advantages?

• Is it just the storage size? An integer foreign key reference is 4 bytes anyways, and a size 2 string is 3 bytes (while a size 5 is 6 bytes - so save 2 bytes at most).

• Is it speed of database? But isn't this then a micro-optimization, which is the "root of all evil"?

• Is it just to make sure that each language "exists" before insertion? There are other mechanisms, and languages should be created on the fly anyway. Existence isn't based on what is in our database, but rather an external lookup to the standards, and the language is properly added once a user provides a single translation.

Examples of the idea were mentioned in the comments on my previous question Improving database design, is this a valid case for entity attribute values?:

language should an int and refer to a seperate table(normalizing your tables) – nbk Jan 13 at 16:54

@nbk interesting -> why? The ISO country + language codes are already unique so a string can just as well be the key right? Why would I use an extra integer redirection, when the textual description is as strong a guarantee of uniqueness? – paul23 Jan 13 at 17:58

@paul23 a normqalized table like in my desription or in the answer helps to reduce size and speed of the querys lets say you have 255 language and you save an int that reduce the size massively. A normalized data design helps noto to have redundant data in your tables – nbk Jan 13 at 18:20

---

The dictionary database is actually separate from the data database, since it is used by several applications.

If I use string keys through a normalizing table, the 'en-us', 'nl-nl'... might very well be perfect keys themselves. They are unique on their own, so why would I use a uuid or other randomized string? In other words, 'en-us' isn't just a string, it's specifically determined by ISO 639 standard as a locale string. Thus, the amount of strings is very much limited. It very much serves as an "identifier" like "pi" is for 3.1415...

Regarding data quality, I wouldn't have to use an integer key — I could use a string — but then the string 'en-us' is the correct string to represent the "English as spoken in US" locale.

Answer 1

Your issue is that you are getting two different pieces of advice conflated into one and the justifications for each piece of advice are not being presented clearly.

Recomendation 1: Normalize your database

In any transactional database this is generally considered a best practice. There are lots of reasons why you might back away from this and there are applications, like BI data warehouses, where this is not necessarily what you want. However, for a transactional database you normalize to begin with and denormalize when necessary as a rule of thumb.

Where there seems to be some confusion is around why to normalize. You are not alone in this confusion. A lot of people have a lot of misconceptions about the purpose of normalizing your database.

Normalization is NOT (primarily) about:

• Increasing performance
• Saving memory
• Saving disk space
• Reducing duplication (it is about reducing redundancy, but that is not exactly the same thing as duplication - more below)

Normalization IS about:

• Data Quality (first and foremost)

Normalizing your database design means that you don't store a piece of data in multiple places so that if you need to update or delete it for any reason you might get yourself in trouble because the data will need to change in more than one place. When you don't normalize you end up with the very real possibility of your data becoming inconsistent over time as changes are made inconsistently. This is a little bit of an over simplification because there are other benefits of normalization, such as simplified query logic. However, data quality is by far the most important benefit and, ironically, it is the one which programmers without formal database design training most often fail to understand clearly.

Recommendation 2: Use Integer Surrogate Keys

This falls much more under the rubric of common custom than anything else. Lots of people like to use a meaningless integer primary key in all their relational database tables. This in itself is actually two pieces of advice: (i) use surrogate keys in every table and (ii) use integers for surrogate keys.

Different people will give you different reasons for why they consider these best practices and all of these reasons can be argued over on a case by case basis. The best argument I can think of for using surrogate keys is that natural keys are more likely to be changed. Changing any primary key is a giant pain so it's best to avoid if you can. The best argument I can think of for using integers for your surrogate key is that it's a nice simple data type which is compact and efficient. Again, this is highly situational so people will make an argument for or against this in different cases.

What I would say about Recommendation 2 overall is this: pick a lane and stick to it so that your code is relatively consistent and diverge from this only when you have a really compelling case of critical performance or critical efficiency and you can clearly demonstrate that diverging from your usual approach has significant benefits.

---

In general, I avoid using natural keys, but there are times when you can get away with it, and even times when it makes better sense to use them. The question you need to ask yourself with a natural key is "Might this change in the future?"

My rule of thumb is "if a user can see it, they're going to want to change it someday". In your specific case though, language codes are set by an international standards body, so the chance that they might change are pretty slim — it would be too big of a pain. I wouldn't hesitate in your case to use "en-us" as a key value.

Answer 2

I'm going to answer your question by following a loose sense of the definition of database normalization (I'm not looking to debate any keyboard warriors on the theoretical textbox definition), since that would be the best way to provide you with a reasonable answer to your practical question. So definitions aside, the root of your question is in practice, what are the benefits of refactoring a string-based column from one table into a separate table with a dedicated integer-based column?.

1. Performance: I'm going to preface this by saying people will debate this til the cows come home because this is the least practical benefit, usually, but it is still an honest and important answer that there truly can be a performance difference under specific conditions. One condition being that the string-based field is used in predicates (JOIN, WHERE, or HAVING clauses). The second condition being that the string-based field typically stores larger values, e.g. a VARCHAR(10485760), which typically will require multiple data pages to store a single value will certainly have more of a factor in performance vs a VARCHAR(10), and may warrant a case of using an integer-based field instead. Thirdly, the size of the table, which will also affect the number of data pages that need to be loaded off disk, can compound the potential performance issues of the previous condition.

   If the right combination of the aforementioned conditions exist, using an integer-based field can improve performance, in practice. I've done so in even less extreme cases where replacing a UUID data type with an integer made a measurable difference in a few large tables. It's certainly more of a micro-optimization, and your mileage may vary. As I mentioned at the start of this point, this is the least practical reason.

2. Flexibility: I'm being a little lazy on this one, instead of re-typing what I've previously written in other answers on this point, here it is directly quoted (link at the end of this answer):

   By having the fields of your data points broken out into appropriately less wide tables, that make general sense to your domain model, and keeping the closely related fields of a particular entity together in the same table, you maximize your ability to utilize, query, and manipulate those data points and entities as needed in your consuming applications.

   An example of this is if you had a Sales Order application that has two screens. One that was the unique list of Items the business sells with their descriptions, and the other was the list of Customer_Sales that have been made by the business so far. If there wasn't a normalized Items table that stored the ItemDesc field then to support both of those screens and their use cases, and you stored the ItemName and ItemDesc directly in the Customer_Sales table, you'd have a more difficult time with the less flexible denormalized Customer_Sale table because of its data redundancy for the Items information.

3. Maintainability: This is the most practical reason, in my opinion, on why refactoring a string-based column into its own table with a dedicated integer-based column is beneficial. Using your example dictionary table stores a language "code", imagine you filled it with 1 million records. Some of the language codes are "en-us", lets say half the table, about 500,000 records, for example. Then if a few months later, the business decides to shorten "en-us" to just "en". Well with this current single table design, it would require us to run an UPDATE statement that would need to modify 500,000 rows.

   In a more refactored design (when it makes sense), where there was a separate languageCodes table, you'd only store the value of "en-us" once and it's integer-based languageId column would be the key that is referenced 500,000 times in the dictionary table. Now to make such a change, you would only need to UPDATE a single record in the languages table, and the language code would automatically be correct for all 500,000 records referencing it from the dictionary table.

   The improved maintainability does lend itself to potential performance improvements too. If your dictionary table is heavily used, an UPDATE to 500,000 rows that will lock that table for an measurable amount of time, might not be conducive to your goals. This goes away when the language code is refactored into its own table.

Again this answer is targeted to the root of your question which is one of practical use cases for refactoring your table structure, loosely in the sense of normalization. For more depth information on the benefits and drawbacks of doing so, please see my other DBA.StackExchange answer.

Finally, I'll conclude by saying, while people will argue that the purpose of true normalization (by the theoretical textbox definition) is not most of what I've discussed in this answer. Rather its main purpose is improved data quality usually through minimizing data redundancy (kind of what my point on maintainability hints at). But in practice, the things I discussed can and generally do become benefits of proper normalization as well.

---

String is the data type, it's irrelevant where that data comes from. I do understand the basis of what you're asking, but unfortunately we can't advise you on the likeliness of a standard changing over time, i.e. what the odds are of 'en-us' becoming just 'en' one day, for example. I'm sure, generally speaking, it's unlikely the ISO standard will change, but it's not impossible and the reasoning I provide for maintainability (and data quality) still holds true then.

It's easier (and can be more performant) to manage less redundant data. So for your specific use-case you may find that you don't care so much about the potential improved data quality and maintainability because you believe the ISO standard has a low chance of changing. But that'll be for you to decide, as with most things database related, it depends.

Answer 3

No. The advice to replace strings with ids and thus "normalize" your database is nonsense. Normalization has several benefits but replacing strings with integer ids is NOT normalization. The comments to use integer ids instead of country/language code should be ignored.

Having lookup tables for country/language has benefits, but using integer ids everywhere just for the sake of some ORMs which can't handle anything else is not good in my opinion. Instead, you can get most of these benefits by using an enum type. Effectively, an enum type is a look up table that the database manages for you. SQL queries will still use strings from the client side, but country/language codes will be checked for existence in the enum, and they'll be represented as integers behind the scenes. The enum values can also be changed or added to, in the off chance the ISO standard eventually changes.

However, it's debatable that you'll have performance gains. If there are any, they will be minor — or you might have gains for some queries and performance loss for other queries.

This answer is an excellent summary. It's worth pointing out that normalization was initially also about first normal form, and that the benefit of 1NF is keyed access to all data. Data in SQL databases is ordinarily in 1NF, but it is possible to subvert the benefit of keyed access to all data by storing a multi-value in a single field.

One might in fact argue that a column with the value 'en-us' violates 1NF. It might be more appropriate to store the parts of the language tags in individual columns.