Register

If this is your first visit, please click the Sign Up now button to begin the process of creating your account so you can begin posting on our forums! The Sign Up process will only take up about a minute of two of your time.

Results 1 to 5 of 5
  1. #1
    WDF Staff smoseley's Avatar
    Join Date
    Mar 2003
    Location
    Boston, MA
    Posts
    9,729
    Member #
    819
    Liked
    205 times
    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:
    Code:
    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:
    Code:
    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:
    Code:
    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:
    Code:
    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:
    Code:
    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:
    Code:
    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:
    Code:
    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!!!

  2.  

  3. #2
    WDF Staff Wired's Avatar
    Join Date
    Apr 2003
    Posts
    7,656
    Member #
    1234
    Liked
    137 times
    Awesome!
    The Rules
    Was another WDF member's post helpful? Click the like button below the post.

    Admin at houseofhelp.com

  4. #3
    Junior Member
    Join Date
    Sep 2008
    Posts
    1
    Member #
    17451
    Brilliant!!

  5. #4
    Junior Member
    Join Date
    Jul 2009
    Posts
    14
    Member #
    19332
    good work!!

  6. #5
    Senior Member Dorky's Avatar
    Join Date
    Jun 2009
    Location
    Destin Florida
    Posts
    1,430
    Member #
    19103
    Liked
    4 times
    one day, i will understand all of that.

    while($get_it !== true){ continue; }


Remove Ads

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

Search tags for this page

create denormalized table using sql query
,
denormalization tutorial
,

how to denormalize a table

,
how to denormalize a table in sql
,

how to denormalize the data from a table

,
mysql denormalizasyon
,

sql denormalization example

,

sql denormalize query

,
sql query to denormalize data
,
sql to make data look denormalized
Click on a term to search for related topics.
All times are GMT -6. The time now is 11:16 AM.
Powered by vBulletin® Version 4.2.3
Copyright © 2019 vBulletin Solutions, Inc. All rights reserved.
vBulletin Skin By: PurevB.com