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

joe.celko_at_northface.edu says...

> 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.

Mr Celko,

Thank you for your reply. Just a couple of clarifications if I may?

1. I presume that by "constraint to enforce his model" you mean a primary key of some sort - i.e. there should be a Transaction_ID field (integer NOT NULL)?
2. The table structure proposed by me was like this:

   ---

Ac_ID  Tr_Date     Tr_Month  Tr_Type  Amount
1      02/01/2003  1         CR       100
1      03/01/2003  1         CR       320
2      03/01/2003  1         CR       500
-----------------------------------------------

Is there not a problem in the fact that the column Tr_Month contains data which is already present in the column Tr_Date, and therefore that introduces a redundancy and hence the table is not properly normalised?

I know from my travels on the internet that you have given courses in how to denormalise tables - is this a "real world" situation where the purist's theory is justifiably put aside in favour of performance/simplification criteria?  

> 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,

Unfortunately, Interbase (so far) is an implementation which doesn't support the CASE construct (though it really, *_really_* should).

Thanks for your input so far.

Paul...

-- 

plinehan__AT__yahoo__DOT__com

C++ Builder 5 SP1, Interbase 6.0.1.6 IBX 5.04 W2K Pro

Please do not top-post.