How do I convert a SQL table to a pointer table?
Let's say I have a SQL table of tuples (id, lastname, firstname, occupation) where all values are strings (ok, obviously id is the key).
I want to convert it to a table with tuples (id, lastid, firstid, occupid) where I only store pointers to other tables containing actual values. We are sorry if the sample domain names and occupations is not suitable for this operation.
Obviously, to create other tables that will store data, I need to get all the names unique and insert them into a new table with an auto-generated key. It's the same with names and occupations.
Having done this, is there a single transformation that can generate a new table containing pointers (er, foreign keys)?
The implementation uses SQLite if it matters.
a source to share
Assuming your tables for last / first names and occupations are Lnt, Fnt and Occ, each with only two columns, an id field and a value:
REPLACE INTO TheTable (last, first, occup)
SELECT Lnt.id, Fnt.id, Occ.id
FROM TheTable
JOIN Lnt ON (last=Lnt.value)
JOIN Fnt ON (first=Fnt.value)
JOIN Occ ON (occup=Occ.value)
a source to share