Re: This table strikes me as wrong - could someone explain why?

From: --CELKO-- <joe.celko_at_northface.edu>
Date: 20 Oct 2003 15:56:56 -0700
Message-ID: <a264e7ea.0310201456.7ebab8c4@posting.google.com>

>> Could anyone explain to me:

what is wrong with the first table structure?

He has split an attribute, so he has two columns, one of which is always zero. Attribute splitting is taking an attribute and making its values into either tables or attributes on their own. Example:

Split a temporal value into tables, as if you were using mag tapes again:

CREATE TABLE Jan_2003 (...); CREATE TABLE Feb_2003 (...);

2) Split a code into columns:
CREATE TABLE Foobar
(..
male CHAR(1) NOT NULL CHECK (male IN ('y','n'), female CHAR(1) NOT NULL CHECK (female IN ('y','n'), ..);

b) if (and how) the second one is better)?

It is in a proper domain key normal form (DKNF); he needs a constraint to enforce his model.

c) how to get the result wanted from the second table structure?

SELECT ...

      SUM(CASE WHEN posting = 'CD' THEM amt ELSE 0.00 END) AS tot_cd,
      SUM(CASE WHEN posting = 'DB' THEM amt ELSE 0.00 END) AS tot_db,
      SUM(CASE WHEN posting = 'CD' THEM amt ELSE -amt END) AS

tot_balance,
FROM ...
WHERE ...; Received on Mon Oct 20 2003 - 17:56:56 CDT