5

In general, I understand that normalization is usually beneficial even with the join costs. However, I came up with an interesting dilemma recently.

What if the data is duplicated but unlikely to ever change. It's possible, but I would not anticipate it.

I have a nutrients table with a unit column and the units would be g, kg, ug, etc.

I can't see these values every changing.

I'm tempted to just put them as a column in the table rather than normalizing and having a units table and using a foreign key and having to join whenever fetching a row from the nutrients table. At the same time, I know in general, even with the join coins, we should normalize.

What should I do (and why)?

Adam Thompson
  • 459
  • 1
  • 4
  • 11

3 Answers3

15

There is a difference between redundant data (bad) and coincidentally repeated data (not bad). Normalization is a technique which is used to avoid insert, update and delete anomalies. It is not meant to eliminate every repetition of a piece of data. Data which is static doesn't benefit from normalization.

A unit of measure, stated as a standardized abbreviation is not the kind of data that you need to normalize out.

Think of it this way: If you were to normalize out your unit of measure into a separate table, you'd need a foreign key from your nutrients table to your units table. Is the unit of measure code going to be unique (probably, yes). Therefore it's a candidate key for your units table. If it's a candidate key in units you could use it as a foreign key in nutrients.

The end result is that you have your unit of measure code in your nutrients table anyway even if you've normalized out the units.

Here's when you would want to create a units table. If you have other predicates (columns) that are dependent on the unit code, but not on the nutrient. For example, a unit_type or a conversion factor to a base unit of the same type (grams for weight, etc.) This would be a transitive functional dependency in your nutrients table and doesn't belong there for that reason.

Joel Brown
  • 12,604
  • 2
  • 32
  • 46
3

Just to add to Joel's excellent answer, here is my take.

As it currently sounds, you don't have to normalize. In this case, you could go with an ENUM type, storing all the possible units you have. This makes sure you don't have garbage inserted as unit.

If there are new units popping up often, this can be a bit cumbersome, otherwise you can add new enum values by

ALTER TYPE units ADD VALUE 'kpc'; -- https://en.wikipedia.org/wiki/Parsec

Answering a concern raised in one of the comments earlier, one can even get the possible values of the type by

SELECT unnest(enum_range(NULL::units));

One case when normalizing really makes sense is when you normalize the units, too: say, you have a thousand of items of the same nutrient in the range of 1 to 100 grams, and you have to calculate the total weight. Based on requirements, you can either come up with an amount like '132456 g', or '132.456 kg'. To do the calculation, you have to keep record of the conversion between the different units, and that will need the separate unit table.

András Váczi
  • 31,778
  • 13
  • 102
  • 151
1

Generally normalizing is better. Here are some pros

  1. Space will be better utilized as indexing will be on integer
  2. Change in the unit code will not impact its transaction table
  3. As you don't know what is bound to happen in the future. you will much lesser change in your code to handle newer unit codes etc
  4. If you are showing in the interface a lookup of units, you need to use the select distinct of the codes. This will be expensive and deteriorate over time based on the volume in the table
srp
  • 121
  • 5