CODEWITHSUNDEEP
sqlsqlite
xApple
xApple

Reputation: 6466

Sqlite3 remove redundancy with extra reference table

So let's say you inherited an SQLITE3 database that has just one table like so:

CREATE TABLE data (id integer, name text)

And it looks like so:

------- DATA -------
89074352  red
12344112  red
47298489  blue
34444333  blue
23453245  red
10000001  yellow
...
--------------------

So the text elements and very redundant. And imagine that the text elements are huge instead of just one word. You would want to avoid that by making a new table that contains every text element only once. The final state of the database would have two tables that look like this:

CREATE TABLE text_keys ("key" integer primary key autoincrement, "name" text not null)
------- TEXT_KEYS -------
1   red
2   blue
3   yellow
...
-------------------------

CREATE TABLE data ("id" integer, "key" integer references text_keys)
------- DATA -------
89074352  1
12344112  1
47298489  2
34444333  2
23453245  1
10000001  3
...
--------------------

What commands would you type in SQLITE3 to go from the first state to the final state of the database ? I've made some research, but have not been able to solve this one.

Upvotes: 0

Views: 99

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270763

First create the reference table, then create the new data table. It would go something like this:

    create table text_keys (
        text_key_id integer primary key,
        name text
    );

    insert into text_keys (text_key_id, name)
        select distinct NULL, name
        from data;

create table new_data as
    select d.id, tk.text_key_id
    from data d join
         text_keys tk
         on d.name = tk.name;

Upvotes: 1

Related Questions