Does this kind of data/field in this table needs normalization in MYSQL?



  • 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.



  • 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.




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2