CODEWITHSUNDEEP
mysqldatabase
Haris Khan
Haris Khan

Reputation: 355

Mysql - normalization of database

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: detail table

so how I accurately normalize my database? Thanks in advance

Upvotes: 3

Views: 420

Answers (2)

Ali Faris
Ali Faris

Reputation: 18630

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

Upvotes: 0

codeLover
codeLover

Reputation: 2592

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

Upvotes: 2

Related Questions