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.