Mysql - normalization of database

Question

I have a MySQL database and I want to normalize it in an efficient way. I have a table in my database in which i am getting data into column in the form of array. Actually I have three tables (details, country, material). In table "country" I have two column that are (id,country_name). In table "material" I have two column (id, material) and in table "details" i have four column (id, vendor, countries, materials). In this scenario, each vendor has multiple countries and materials and I am inserting ids of country and material in details table. Here is the screenshot:

so how I accurately normalize my database? Thanks in advance

You need join tables: details_country and details_material. These tables would store IDs only, the material ID and the detail ID (or country ID and detail ID). Drop countries and materials columns from detail. — marekful
– marekful, Commented Jan 24, 2018 at 5:54
In the sample record you showed us, is it the case that all countries are always associated with all materials? Or is that not the case? Also, continuing with your current design, would there ever be the possibility of a vendor having more than one record in the details table? — Tim Biegeleisen
– Tim Biegeleisen, Commented Jan 24, 2018 at 5:54
no there is no relation with countries and materials. also each vendor has only one record in detail table. there is no duplication of vendor in detail table. — Haris Khan
– Haris Khan, Commented Jan 24, 2018 at 5:57
If I drop these two column from detail table then how I will get vendor details using joins? — Haris Khan
– Haris Khan, Commented Jan 24, 2018 at 6:00

codeLover · Accepted Answer · 2018-01-24 05:55:12Z

2

Remove countries and materials columns from Details table.

You can create a fourth table which will depict the relationship between vendor,country and material:

vendor_id   country_id   material_id
   1            3            5
   1            6            9
   1            7            24

Here all the columns will be foreign key and together they will form composite primary key

answered Jan 24, 2018 at 5:55

codeLover

2,5921 gold badge13 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Tim Biegeleisen Over a year ago

I'm not sure we need a fourth table, i.e. the above table you are suggesting can becomes the new details table +1

codeLover Over a year ago

In that case vendor name would get repeated in each row leading to data redundancy. As per the data shown in question a vendor can deal in multiple countries and in multiple materials.

Tim Biegeleisen Over a year ago

You're right...I wasn't thinking of the vendor name. Yes, there should be a separate vendor table.

Ali Faris · Accepted Answer · 2018-01-24 06:03:25Z

0

you need to design two new tables detail_country and detail_material also you need to modify your detail table as fellow

detail           : id , vendor

detail_country   : detail_id , country_id

detail_material  : detail_id , material_id

answered Jan 24, 2018 at 6:03

Ali Faris

18.7k12 gold badges51 silver badges75 bronze badges

Collectives™ on Stack Overflow

Mysql - normalization of database

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related