• To normalize...

    From Angus McLeod@VERT/ANJO to All on Tuesday, September 12, 2006 23:36:00
    ... or not to normalize!

    I'm collecting some WX data from various points around the net, and
    bunging the data points into a database. Inserts look something like
    this:

    INSERT IGNORE INTO Conditions SET
    Barometer_inHg = 29.95,
    Barometer_mb = 1014,
    Dewpoint_C = 24,
    Dewpoint_F = 75,
    Heat_index_C = 31,
    Heat_index_F = 88,
    Humidity = 84,
    Sky_conditions = 'Mostly Cloudy',
    Temperature_C = 27,
    Temperature_F = 81,
    Updated = '2006-09-12 23:00',
    Visibility_Km = 10.0,
    Visibility_Mi = 6.2,
    Wind_direction = 110,
    Wind_direction_text = East, Southeast,
    Windspeed_Km = 13,
    Windspeed_Mi = 8
    ;

    Now, I'm also retrieving cloud-cover data that looks like this:

    Few at 1200 ft (365 m)
    Scattered Clouds at 1400 ft (426 m)
    Mostly Cloudy at 28000 ft (8534 m)

    Number of cloud levels can vary from zero (no clouds) upwards to ???
    different levels. Each level will consist of a description, like
    "Scattered Clouds" and an altitude in feet and/or meters. My immediate
    urge is to define a separate table, with a timestamp in each row, then do
    as many inserts (Zero+) as there are levels.

    INSERT INTO Cloudcover VALUES
    ('2006-09-12 23:00', "Few", 1200, 365),
    ('2006-09-12 23:00', "Scattered Clouds", 1400, 426),
    ('2006-09-12 23:00', "Mostly Cloudy", 28000, 8534)
    ;

    and retrieve them as needed with something like

    SELECT Description, Feet, Meters
    FROM Cloudcover
    WHERE Timestamp = '2006-09-12 23:00'
    ORDER BY Feet
    ;

    Can anyone think of any reason NOT to do it this way, but to denormalize
    my data and add a few sets of columns in ther main table for this data?
    ---
    Playing: "Invaders" by "Iron Maiden"
    from "The Number of the Beast" album
    þ Synchronet þ Programatically generated on The ANJO BBS
  • From Deuce@VERT/SYNCNIX to Angus McLeod on Tuesday, September 12, 2006 23:04:00
    Re: To normalize...
    By: Angus McLeod to All on Tue Sep 12 2006 11:36 pm

    Can anyone think of any reason NOT to do it this way, but to denormalize
    my data and add a few sets of columns in ther main table for this data?

    Only if you're using a database which doesn't allow cascading deletes. Quite often it makes sense when using cripped database systems which aren't quite relational.

    I don't know if any general-use systems fit this bill anymore though, iirc even MySQL has this.

    ---
    Wheeble.

    ---
    þ Synchronet þ My Brand-New BBS (All the cool SysOps run STOCK!)
  • From Angus McLeod@VERT/ANJO to Deuce on Wednesday, September 13, 2006 08:21:00
    Re: To normalize...
    By: Deuce to Angus McLeod on Tue Sep 12 2006 23:04:00

    Can anyone think of any reason NOT to do it this way, but to denormalize my data and add a few sets of columns in ther main table for this data?

    Only if you're using a database which doesn't allow cascading deletes. Quit often it makes sense when using cripped database systems which aren't quite relational.

    Yeh, pretty much my thought.
    ---
    Playing: "Flowers In The Window" by "Travis"
    from "The Invisible Band" album
    þ Synchronet þ Programatically generated on The ANJO BBS
  • From Tracker1@VERT/TRN to Deuce on Friday, September 15, 2006 23:50:00
    Deuce wrote:
    Only if you're using a database which doesn't allow cascading deletes. Quite often it makes sense when using cripped database systems which aren't quite relational.

    I don't know if any general-use systems fit this bill anymore though, iirc even
    MySQL has this.

    mySQL (at least last time I worked with it, early 5.x versions) queries
    with joins are exceedingly slow.. so that may still affect desire to denormalize. In any case, when views are available, may not be worth
    debating depinding on use...

    --
    Michael J. Ryan - tracker1(at)theroughnecks(dot)net - www.theroughnecks.net icq: 4935386 - AIM/AOL: azTracker1 - Y!: azTracker1 - MSN/Win: (email)

    ---
    þ Synchronet þ theroughnecks.net - you know you want it
  • From Deuce@VERT/SYNCNIX to Tracker1 on Saturday, September 16, 2006 03:06:00
    Re: Re: To normalize...
    By: Tracker1 to Deuce on Fri Sep 15 2006 11:50 pm

    Only if you're using a database which doesn't allow cascading deletes.

    I don't know if any general-use systems fit this bill anymore though,
    iirc even MySQL has this.

    mySQL (at least last time I worked with it, early 5.x versions) queries
    with joins are exceedingly slow.. so that may still affect desire to denormalize. In any case, when views are available, may not be worth debating depinding on use...

    We're talking apples and oranges here... I was just speaking of cascading deletes. That is to say, when I delete the info for one day in the main table, the info for that day is automatically deleted in the cloud cover table. Referential integrity is the keyword to dig for.

    ---
    Wheeble.

    ---
    þ Synchronet þ My Brand-New BBS (All the cool SysOps run STOCK!)