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

Question

1.00/5 (1 vote)

See more:

I have this table in MYSQL example1[^] and I know it is better to normalize this table.

Now I have another products table (that is not related to the table above, the table above is just an example) example2[^] Does this table needs normalization? Some people said that this table doesn't have multi-value field/data... Is that correct? The table in short, has name, title and a uom column of a product. Uom column has unit of measurement, quantity package, and price based on the unit. That means each product has different unit and prices based on the unit. For example for test4 product 1:1:10.00,2:12:20.00,3:1:30.00, we'll take the first part 1:1:10.00 the 1st number is the id of the unit in the units table, the 2nd number is the quantity package, the 3rd number is the price, so this 1:1:10.00 is one unit. 2 and 3 are other units, so test4 has 3 different units.

What I have tried:

Does this table needs normalization?

Posted 8-Mar-22 1:50am

FRS4002

Updated 8-Mar-22 6:51am

v3

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Answer 1 · 2022-03-08T06:17:00

Solution 1

It's very hard to say if your database needs normalization, because you showed us only the content of one table.

If you want to learn about Sql normalization, see: what is Normalization in SQL? 1NF, 2NF, 3NF and BCNF | Simplilearn[^]
Database Normalization in MySQL - Dot Net Tutorials[^]

Good luck!

Posted 8-Mar-22 6:17am

Maciej Los

Comments

FRS4002 9-Mar-22 8:39am

If you mean uom table, the uom table has only the id and the uom, nothing else. I think uom table won't affect here...

Maciej Los 9-Mar-22 14:45pm

Not a single table. Entire database.

FRS4002 9-Mar-22 14:51pm

There are exactly 2 tables only in the database uom table and products table. Uom table has id and uom only, products table is available in the original post.

CHill60 · Answer 2 · 2022-03-08T06:51:00

Solution 2

Quote:
Uom column has unit of measurement, quantity package, and price based on the unit.

That is a very poor design. You should have a separate column for each of unit of measurement, quantity package and price.

Then you can see where you would need normalisation.

Imagine you have a product table - it contains things that are specific to the product such as name, possibly colour, supplier etc.

If each product has a different price depending on UOM then each of those prices should have it's own row on a table. But you don't want to be repeating the name of the product, the colour, the supplier.

So - Product table could have (item number, supplier, name) then another table will have (item number, UOM, Price) where the item number on the 2nd table is the foreign key to the product table

E.g. 1, Supplier1, Test4 on product table
then Price table could have
1, 'Metre', 1.34
1, 'Yard', 4.02
1, 'Widget', 100.98

etc etc

Similarly with the colours - unless Red/Green is a colour. But if the item can be in Red OR Green you should have a Colours table e.g. Id, colourname, RGB and then a table that links those colours to a product (productID, ColourID) with one row for each possible colour

Posted 8-Mar-22 6:51am

CHill60

Comments

FRS4002 9-Mar-22 8:37am

Ok, can't I normalize it in one table? Is that an issue if the name and supplier is repeated? Some people said you can normalize on the same table or by splitting it into 2 tables, is that correct? Which way to use?

CHill60 9-Mar-22 11:47am

See links from Maciej - it explains it quite well.
Here is your table

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

Step 1 towards First Normal Form : disallows multi-valued attribute etc

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

Step 2 - First Normal Form

id	itemNo	title	uom	quant	price
84	19421	test1	1	1		10.00
84	19421	test1	3	1		56.17
88	7878	test2	1	1		24.33
89	123456	test3	2	12		63.22
89	123456	test3	3	1		14.00
90 666565	test4	1	1		10.00
90 666565	test4	2	12		20.00
90 666565	test4	3	1		30.00

See how much easier it is to find the price for 12 items of test3?
This is what you are referring to as "normalize on one table"

But you should go further to remove all that repetition. It is not precisely an issue unless you have large tables (uses up space and slows performance) but you will fail your course if you stop there.
Two tables are required one for the items and 1 for the prices
Items

id	itemNo	title	
84	19421	test1	
88	7878	test2	
89	123456	test3	
90 666565	test4

Prices

id	uom	quant	price
84	1	1		10.00
84	3	1		56.17
88	1	1		24.33
89	2	12		63.22
89	3	1		14.00
90 	1	1		10.00
90 	2	12		20.00
90 	3	1		30.00

FRS4002 9-Mar-22 13:45pm

Thanks a lot for clarifying the matter, but I have a lot of questions regarding on what you post.
1-In step 1 and step 2 I don't see a primary key at all... Is that normal? And what if I need to add a primary in the same table, how this could happen?
2-In step 1, you considered that uom column is free of multi-value? That means 1:1:10.00 itself is not a multi-value? I thought uom column in step 1 is still has multi-value, it has uomid, quantity package and price. Why didn't you skip step 1 and head to step 2 directly?
3-When you split step 2 into 2 tables Items and Prices, is it still in 1st normal form?
4-In Prices table, the id is repeated, as if the itemNo and title is repeated in step 2 but with the id. So, the repetition is still there...
5-Same question of number 1 but in Prices table regarding primary key.

CHill60 9-Mar-22 13:53pm

1. Surely id is the primary key? It's your table
2. I would normally have skipped step 1, I only included it to try to clarify the process
3. Each of the normalisation forms requires the previous form to have been met, so yes
4. The prices table should be keyed on id AND uom ...one record per id per uom. That's how foreign keys work.
5. Primary key is id + uom
Read the links from Maciej. This is not a tutorial forum

FRS4002 9-Mar-22 14:06pm

First of all, I read the links, honestly I didn't understand, it is a little bit confusing. Because, I am getting different info from different links/people.
For point 2, uom in step 1 is considered has multi-value or not? And if not, why?
For point 5, 2 columns are primary keys? Is that normal? And why uom is also primary key with id?
Also, what is the role of 2nd normal form in this 2 tables, Items and Prices? Does 2nd normal form apply to these tables?

CHill60 10-Mar-22 6:33am

yes - uom in step 1 is still multi-value. If we think of uom as a class or a structure, then in the original data some of the rows had multiple uom instances in a single column. I separated those out into 1 row having only 1 uom structure instance. But each uom structure contains three values - i.e. the uom column is still multi-valued. So to get to 1NF I separated the uom column out into its component parts of [unit of measurement], [quantity in package],[Price]. I didn't get any extra rows because of step 1, just extra columns.
For point 5 - it's not that 2 columns are primary keys. A table can only have one primary key. In this case the primary case consists of 2 columns - it's known as a Composite key. Here is a link that will probably be better at explaining it than me - Primary key on two columns sql server[^]. It is about what makes this row unique - in this case we can't use id because each item can have many units of measurement so id will not be unique in the table. Similarly each uom may be "used" by many products, so by itself uom is not unique. But together they are unique - each combination of id and uom is unique. Does that help?

FRS4002 10-Mar-22 9:05am

Yes! I understand the composite key now! You made the id a primary key because the value in it is the value of a primary key in Items table which is id. Same thing for uom in Prices table, the id is also a primary key because it gets the value from a primary key from another table which is uom table. So, in both columns we are actually adding ids/primary keys of another tables! Since we have 2 primary keys in the table, we call them composite key. Ok, then there will never be an autoincrement to any column even the primary keys in Prices table, right? Also, please you didn't answer my question above regarding 2nd normal form... "What is the role of 2nd normal form in this 2 tables, Items and Prices? Does 2nd normal form apply to these tables?" and why?

CHill60 10-Mar-22 12:02pm

Now that I have seen your comment to Maciej about the uom table then, no, these are not in 2NF because [quant] is functionally dependent on [uom]. Your uom table should probably look like this

uom	quant [name of unit]
1	1
2	12
3	1

And the prices table

id	uom	price
84	1	10.00
84	3	56.17
88	1	24.33
89	2	63.22
89	3	14.00
90 	1	10.00
90 	2	20.00
90 	3	30.00

FRS4002 11-Mar-22 7:37am

I don't know if it is a good idea, but here is the usage of quantity package. Quantity is different than quantity package, so there is a column called quantity and another column called quantity package (qp). I am using quantity package to calculate the remaining quantity of a product. Because some products could have multiple uom. Therefore, I need to unite the database to one uom which is pieces. Uom 1 is piece, uom 2 is dozen, uom 3 is set. So, when a product with a quantity of 50 has multiple uom let us say piece and dozen so, 2 dozens x 12 = 24 piece. 12 is the qp. But if I have 1 uom for a product like dozen only so, 2 dozens x 1 = 2 dozens. Therefore, uom 2 could have 1 or 12 as a qp. I hope you understand. I don't know if you have a better way of doing this, please share it to me. Thanks.