Hi All.

I have a very large single-table database of articles that I want to convert to a multi-table, relational database.

The existing single-table database contains fields for article author, article source, and article category, where several 'author', 'source', and 'category' IDs repeat dozens of times for hundreds of different articles.

I want to create seperate tables for author, source, and category and populate the new tables by extracting data from the original single-table database by unique ID field.

I figured out how to use INSERT and SELECT to pull data in new tables, but can't figure out how to pull only a single instance of a unique author, source, and category to create master reference tables for author/source/category.

Any assistance would be much appreciated.

Thanks in advance.

L.B.
lbfrank@envoyoffice.com

Maybe try this:
-- This creates the table
CREATE TABLE author(
author_id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
name varchar(50) NOT NULL
)

-- This inserts all the existing author, only once
INSERT INTO author ( name)
SELECT DISTINCT author_name FROM articles

-- This creates a column in articles table to hold the recently generated author_id

ALTER TABLE articles ADD author_id int NULL

-- Add the author_id in articles table

UPDATE articles
INNER JOIN author ON articles.author_name = author.name
SET articles.author_id = author.author_id

-- You may want to ensure that all worked well, and do a backup before this last step

ALTER TABLE articles DROP author_name

Repeat this steps for source and category as well. Hope this helps!