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

>> 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 [sic](integer NOT NULL)? <<

Columns are not fields; rows are not records and tables are not files.  But yes, a transaction identifier is probably part of the process and you want to record it. And a lot more constraints than just that are needed.

>> 2) The table structure proposed by me was like this: <<

Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, datatypes, etc. in your schema are. And I hop you know that Standard SQL uses the ISO-8601 date format instead of what you wrote.

Try something like this:

CREATE TABLE Transactions
(trans_id INTEGER NOT NULL PRIMARY KEY,
 acct_nbr DECIMAL (9) NOT NULL

       REFERENCES Accounts (acct_nbr)
       ON UPDATE CASCADE,

          CHECK(trans_type IN ('CR', 'DB')),  amount DECIMAL(12,2) NOT NULL

        CHECK (amount >= 0.00));

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

It is not normalized and you need another table in the schema; a Calendar table keyed on the calendar date, with all the temporal information the enterprises requires (fiscal years, holidays, etc.). Do a JOIN to the Calendar to get the reporting week, month, quarter or year.

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

I mention denormalization when I was doing SQL classes and put in the same category as an emergency tracheotomy -- it is not the first thing you do when someone coughs. The Calendar table will work fine for your situation and it will be very, very useful in the system as a whole. Received on Thu Oct 23 2003 - 18:30:56 CEST