4

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)?

  • 1
    Why would you have to join every time? – ypercubeᵀᴹ Mar 24 '18 at 10:35
  • 2
    Would all unit names be that short in your case? – Andriy M Mar 24 '18 at 11:00
  • @AndriyM yes they would be that short. – Adam Thompson Mar 24 '18 at 19:04
  • 1
    @AdamThompson can you edit the question and add exampple (of code and data you would need)? It's not exactly clear what would be needed, beside the unit, eg. the 'kg'. – ypercubeᵀᴹ Mar 24 '18 at 20:58
  • 3
    Hi. Replacing a column of values by another column of values that are ids plus a lookup table is not (either of the 2 things typically meant by) "normalization". Find out what normalization is. – philipxy Mar 26 '18 at 18:45
10

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.

  • What if I ever need to change units in the future? I will need to change every row rather than just whatever the FK would point to? Is that considered a good enough benefit to go for normalization? – Adam Thompson Mar 24 '18 at 19:02
  • 2
    @AdamThompson You have to assess the risk of your unit of measure code changing. Let's say that for some reason, the whole world decides that the symbol for gram is going to change from g to gr. If that happened and you weren't using a meaningless foreign key for your unit of measure, then you'd have to run a single update statement against your nutrients table to make this change. Is that so bad? That's what you have to decide. All design is trade-offs. I personally would not normalize units of measure. They really aren't going to change and if they do it's no big deal. – Joel Brown Mar 24 '18 at 21:05
  • 1
    6th normal form eliminates all redundancies, except that orthogonal tables are allowed. But this is just a detail which doesn't really affect the quality of your answer. – Federico Razzoli Mar 28 '18 at 0:19
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.

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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