First Normal Form

First Normal Form

A table is in first normal form (1NF) when each column holds one atomic value, no group of columns repeats (author1, author2), and a key identifies every row. The authors cell fails: finding Omar Haddad's books takes LIKE '%Haddad%', which cannot use an index and would match "Haddadi" too. Give each value its own row; JSON_TABLE (JSON_TABLE) splits all lists at once:

Splitting a comma-separated cell into one row per valueSQL

CREATE TABLE sku_authors (PRIMARY KEY (sku, author)) AS
SELECT DISTINCT s.sku, TRIM(j.author) AS author
FROM sheet AS s, JSON_TABLE(CONCAT('["', REPLACE(s.authors, ',', '","'), '"]'),
                            '$[*]' COLUMNS (author VARCHAR(40) PATH '$')) AS j;
ALTER TABLE sheet DROP COLUMN authors;
SELECT author, GROUP_CONCAT(sku ORDER BY sku) AS skus
FROM sku_authors GROUP BY author HAVING COUNT(*) > 1;

Output

+-------------+---------------------+
| author      | skus                |
+-------------+---------------------+
| Omar Haddad | BK-LAR-01,BK-PHP-01 |
| Rui Costa   | BK-SQL-01,BK-SQL-02 |
+-------------+---------------------+

Authors with several books are now a plain GROUP BY, and a join maps these rows onto product_authors (ER Model to Tables). Keep a list as JSON only when you never join, count or constrain its elements, as with products.attributes.

MENU