Re: Help using MySQL reformatting data

[Zach Ellis]

Hello Philip Ward

I'm probably thinking about this wrong. But if table one is the old format with the state, city, convenient store and many sku product columns

the old skus columns are like a boolean column with 1 or 0 so when you union them
all the skus columns will be 1 or 0 into the new sku column you wont be able to identify the different products. Only the state, city, convenient store will be different.

from Nirgal about it being a boolean
"But instead of using a single column for product, they made each product its own column and put a "1" if the product was present. This was done because the same convenient store can have many skus".

example with your method as with the current data being inserted into the new table

data in current table
State - city - convinient store - chocolate_sku - strawberry_sku - cookiescream_sku
NY - New York - store_1 - 1 - 0 - 1

data in new table 2
state - city - store - sku
NY - New York - store_1 - 1 you now dont know what item is what since the column name is the unique id
NY - New York - store_1 - 0
NY - New York - store_1 - 1

the problem with the old table as far as i understand. The column name is being used as the identifier instead of a unique number that references to another table with a unique id for the sku products. So even if you change the number to a unique number you wont know which item is present based off the old table structure which is being copied. All the rows will have the new unique number for each sku column and you wont know which item was present in the store.

example of the current table structure
State - city - convenient store - chocolate_sku - strawberry_sku - cookiescream_sku
NY - New York - store_1 - 1 - 0 - 1
how the table would look like where the 1 is the item is present in the store and 0 is the store dosent have it and the column name identified the item

example

if the column had unique ids while still having the old structure
State - city - convenient store - chocolate_sku - strawberry_sku - cookiescream_sku
NY - New York - store_1 - 1 - 2 - 3
now the table dosent work the store_1 based off the old data and how it worked the store now has the strawberry item in the store and all the other items which based off the old data it didn't have it.

you would have to create a new table with the structure of table 2 and use a primary key from another table that uniquely identifies the item and has columns about the item like the name and the sku number.

Not good at summarizing things sorry about the long post.

regards
Zach Ellis