Search code examples
mysqlpandasdatabase-normalization

Normalize MySQL Database by assigning unique IDs to certain columns, then move relevant information to new table

I have one table with all of the pertinent information I need for my database with the columns: CompanyName, StoreLocation, StoreNumber, ItemName, and ItemModel

One Table ERD

I want to move the column CompanyName to its own Company Table that will contain a CompanyID and the CompanyName in its own table, as well as moving the StoreLocation and StoreNumber columns to their own Store Table. I need to assign unique (auto-incrementing is fine) IDs for each company and for each store while maintaining foreign-primary key integrity for the IDs in each table. After making these changes the database ERD should look something like this:

Normalized ERD

Here is the expected output for all of the data in the one table:

One Table Output

And lastly, here is the expected output when all of the tables are normalized:

Normalized Output

What is the best way to go about doing this? I can use Python's Pandas if necessary, but was wondering the best way to add this normalization in to avoid the duplication of many values if it were to stay in one table.

Let me know if I can provide any additional information.

Solution

  • This can be done with 3 SQL queries:

    INSERT INTO company (companyname)
SELECT DISTINCT companyname
FROM originalTable;

INSERT INTO store (storelocation, storenumber, companyid)
SELECT DISTINCT o.storelocation, o.storenumber, c.companyid
FROM originalTable o
JOIN company c ON o.companyname = c.companyname;

INSERT INTO item (itemname, itemmodel, storeid)
SELECT o.itemname, o.itemmodel, s.storeid
FROM originalTable o
JOIN store s ON s.storelocation = o.storelocation AND s.storenumber = o.storenumber
JOIN company c ON s.companyid = s.companyid AND o.companyname = c.companyname;

    The join with company in the third query is necessary if the original table can have duplicate location+storenumber for stores from different companies.