Search code examples
sqlsql-serverdatabaset-sqldatabase-normalization

Normalizing a table with different structures

I have a normalized table (table a) and a non-normalized table (table b), like this:

enter image description here

I want to insert a new row into the normalized table for each parent in the non-normalized table. So if there is a record in table B with two parents for a studentID, I want to insert two records into table A with each different parent name from table B.

I've been struggling for with this for hours, any help would be appreciated on how to go about doing this.

Solution

  • Assuming parentID is an auto incremental column, you could do this:

    INSERT INTO NormalizedFamily(studentID, parentName)
SELECT studentID, parentName1
FROM FlatFamily
UNION
SELECT studentID, parentName2
FROM FlatFamily
WHERE parentName2 IS NOT NULL
UNION
SELECT studentID, parentName3
FROM FlatFamily
WHERE parentName3 IS NOT NULL
UNION
SELECT studentID, parentName4
FROM FlatFamily
WHERE parentName4 IS NOT NULL

    In any case, I don't really get why the column parentId is the key of your normalized table. As it is, the same parent can't have multiple students related to him?, shouldn't the key be a combination of parentID and studentID?.