MySQL Tutorial: How to Denormalize a Normalized Table Using SQL

[smoseley]

How to Denormalize a Normalized Table Using SQL

by Steven Moseley

Often times, we sacrifice some usability in our data in order to follow nth-normal form in our database architecture.

The following is a trick that allows us to retrieve name-value pairs of data as denormalized column-value pairs.

Let's take the case of a user settings table. A typical denormalized user settings table may look like this:

CREATE TABLE denormalized_settings (
user_id int NOT NULL,
setting1 varchar(255) NULL,
setting2 varchar(255) NULL,
setting3 varchar(255) NULL,
setting4 varchar(255) NULL
);

INSERT INTO denormalized_settings (user_id, setting1, setting2, setting3, setting4) VALUES
(1, 'hi', '0', '1', '0'),
(2, '0', '1', NULL, '1'),
(3, '1', '0', '1', NULL);

This table will create one record for each user, with the setting values placed in appropriate columns. The data will look something like this:

user_id setting1 setting2 setting3 setting4
------- -------- -------- -------- --------
1 hi 0 1 0
2 0 1 NULL 1
3 1 0 1 NULL


By normalizing our table, we allow flexible control of settings through meta-data identifiers, rather than table definition. The following would be a proper 2nd normal form settings table design:

CREATE TABLE normalized_settings (
user_id int NOT NULL,
name varchar(255) NOT NULL,
value varchar(255) NULL
);

INSERT INTO normalized_settings (user_id, name, value) VALUES
(1, 'setting1', 'hi'), (1, 'setting2', '0'), (1, 'setting3', '1'), (1, 'setting4', '0'),
(2, 'setting1', '0'), (2, 'setting2', '1'), (2, 'setting3', NULL), (2, 'setting4', '1'),
(3, 'setting1', '1'), (3, 'setting2', '0'), (3, 'setting3', '1');


We could even go a step further and define a "setting_types" table, in order to be third normal form compliant (no repeating identifiers in the "name" column).

This is what our final "settings" database could look like:

CREATE TABLE users (
id int NOT NULL,
username varchar(255) NOT NULL,
PRIMARY KEY (id)
);

INSERT INTO users (id, username)
VALUES (1, 'user1'), (2, 'user2'), (3, 'user3');

CREATE TABLE setting_types (
id int NOT NULL,
name varchar(255) NOT NULL,
PRIMARY KEY (id)
);

INSERT INTO setting_types (id, name)
VALUES (1, 'setting1'), (2, 'setting2'), (3, 'setting3'), (4, 'setting4');


CREATE TABLE user_settings (
user_id int NOT NULL REFERENCES users (id),
setting_type_id int NOT NULL REFERENCES setting_types (id),
value varchar(255) NOT NULL,
PRIMARY KEY (user_id, setting_type_id)
);

INSERT INTO user_settings (user_id, setting_type_id, value)
VALUES
(1, 1, 'hi'), (1, 2, '0'), (1, 3, '1'), (1, 4, '0'),
(2, 1, '0'), (2, 2, '1'), (2, 4, '1'),
(3, 1, '1'), (3, 2, '0'), (3, 3, '1');


So now, what we get is a user_settings table that has very strict data definition, and looks something like this:

user_id setting_type_id value
------- --------------- -----
1 1 hi
1 2 0
1 3 1
1 4 0
2 1 0
2 2 1
2 4 1
3 1 1
3 2 0
3 3 1


But let's say that our application needs to retrieve the normalized data in one row per user.... well then we're stuck... right? Wrong! This is where we have to get creative! :-)

The following SQL query will denormalize your data for you:

SELECT
us.user_id,
MAX(CASE WHEN st.name = 'setting1' THEN us.value ELSE NULL END) AS setting1,
MAX(CASE WHEN st.name = 'setting2' THEN us.value ELSE NULL END) AS setting2,
MAX(CASE WHEN st.name = 'setting3' THEN us.value ELSE NULL END) AS setting3,
MAX(CASE WHEN st.name = 'setting4' THEN us.value ELSE NULL END) AS setting4
FROM
user_settings AS us
INNER JOIN setting_types AS st ON us.setting_type_id = st.id
GROUP BY us.user_id


And the results look something like this:

user_id setting1 setting2 setting3 setting4
------- -------- -------- -------- --------
1 hi 0 1 0
2 0 1 NULL 1
3 1 0 1 NULL


Look familiar? (Check the data of the first table again!) :-D

So there you have it... some clever grouping and SQL data manipulation, and you've denormalized your properly normalized table.

God, I love this stuff!!!

[Wired]

Awesome!

[atreu59]

Brilliant!!