2

I have this uom table in MYSQL:

id uom
1 piece
2 dozen
3 set

and this products table in MYSQL:

id itemNo title uom
1 19421 test1 1:1:10.00,3:1:56.17
2 7878 test2 1:1:24.33
3 123456 test3 2:12:63.22,3:1:14.00
4 666565 test4 1:1:10.00,2:12:20.00,3:1:30.00

Does this products table needs normalization? Some people said that this table doesn't have multi-value field/data... Is that correct? The products table in short, has name, title and a uom column of a product. Uom column has unit of storage, quantity package, and price based on the unit. That means each product has different unit and prices based on the unit.

For example, the test4 product has three units 1:1:10.00,2:12:20.00,3:1:30.00. We'll take the first unit, as an example: 1:1:10.00.

  • 1st number is the id of the unit in the units table
  • 2nd number is the quantity package
  • 3rd number is the price

So, 1:1:10.00 is one unit. 2:12:20.00 and 3:1:30.00 are different units. So, test4 a total of 3 units.

2

2 Answers 2

5

I would vote yes to normalize it. Units of measure, i.e. a UOM column, should literally be units of measure, e.g. "meters", "yards", "pounds", "tons" etc. I don't see that at all in the values you're currently storing in it, such as 1:1:10.00. You even say it yourself, that these are different data points "3rd number is the price". Then you should likely have a Price column instead. If you want to store just the ID of the UOM in the UOM column, that's fine, but I'd recommend normalizing the other data points appropriately.

By stuffing multiple data points into one column, you hurt the readability, maintainability, and query-ability of that column. It could even affect performance if you need to run functions against that column to parse certain data points from it, especially if those data points are used in predicates (JOIN, WHERE, HAVING clauses) anywhere in your queries.

10
  • Ok, then which normal form should I choose to normalize?
    – Algo
    Commented Mar 8, 2022 at 13:19
  • 2
    I second this answer. Commented Mar 8, 2022 at 13:24
  • @BrendanMcCaffrey You second this answer?
    – Algo
    Commented Mar 8, 2022 at 13:29
  • 1
    @Algo Yes, I agree with J.D.'s answer. You need to break the columns out. It's how I would have answered the question, had he not already done so. Commented Mar 8, 2022 at 13:39
  • @Algo I personally wouldn't focus on particular levels of normal form. While good in theory, sometimes it's simpler in practice to just stick to a basic principle of one-field one-purpose. So each of those data points, in my mind, should get their own field. Whether that's in the same table or a different table, more so to me depends on if they make logical sense (by relevancy) as part of the same object or not if you think about the concrete form when you instantiate an entity.
    – J.D.
    Commented Mar 8, 2022 at 13:58
1

Does this table needs normalization?

Short answer: yes.

Slighlty less short answer: normalization is not something a database table "needs", but in some cases (like this one) normalization is highly recommended as it will bring a lot of benefits.

Some people said that this table doesn't have multi-value field/data... Is that correct?

It's not correct. Not only does this table have a multi-value column, the values of this multi-value column are entities on themselves, with their own set of values. And unless you're using a column type that is meant to have complex structures as values (say, a JSON column), this kind of column makes overly complicated to manipulate and process its data.


Now, the drawback: changing the data structure would be a breaking change, so if this data is read as-is an parsed in a lot of different places, then this would require a lot of changes across a lot of modules, then the benefit could come at a relatively high cost. You need to leverage if the normalization is worth it i.e. if the changes are not that complicated, or if there's risk that keeping the data structure as it is now would eventually make the information unreadable and unmaintenable if/when it grows too big.

1
  • Ok, then which normal form should I choose to normalize?
    – Algo
    Commented Mar 9, 2022 at 13:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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