Let's say you have the following data:
NAME 200812 200901 200902
--------- ---------- ---------- ----------
Cable 75 75 75
Groceries 250 225 300
Internet 50 50 50
Mortgage 1000 1000 1000
Phone 25 25 25
The table structure for that would look like the following:
CREATE TABLE t
(
name VARCHAR2(30),
"200812" NUMBER(6,0),
"200901" NUMBER(6,0),
"200902" NUMBER(6,0)
);
Is this the best way to store it though?
Are there times when you
would want to store it this way?
What happens when March (200903) rolls around? Well, you'll have to add another column. Not so bad you say? But you'll have to do that same thing the following month, the month after that and so on. Now you have 5 years (60 months/columns) worth of data. Your table becomes a bit unwieldy and difficult to maintain. Not to mention the eyesore of a 60+ column (and ever growing) table.
What if you find a mistake in 200901, Groceries should have been $400. That's easy enough to update right?
UPDATE t
SET "200901" = 400
WHERE name = 'Groceries';
That's OK for the occasional update I suppose, but what if you wanted an interface through PL/SQL? Each time you add a column you'd have to update the code as well. That sucks.
I suppose you could write some Dynamic SQL to generate the UPDATE statement each and every time, but Dynamic SQL isn't very easy to maintain.
Now, what if you needed to know the history for the table. What would you do?
Well, you could create a history table and a trigger (blech) to populate it on UPDATE and DELETE. Not so bad right?
You'd then be able to query both tables to find the history of a particular column. Your SQL (reports) will have to updated and may get pretty complicated as well.
But wait. You have to add a column for each month which now needs to propogate to:
1. Source Table
2. PL/SQL (unless of the Dynamic variety)
3. Trigger
4. History Table
5. Reports.
How do you make this easy?
I want to write this one time and just let it be. I don't want a history table (nor the accompanying trigger, blech). I don't want to rewrite my code or my SQL each and every time a new month comes.
The underlying model should handle most circumstances and your reports should just work.
First, I want to constrain my "name" list. I could use a CHECK constraint to do so, but I'd like to be able to add to it. Let's create a reference/lookup/crosswalk (not to be confused with an intersection) table.
CREATE TABLE name_types
(
namecode VARCHAR2(30)
CONSTRAINT pk_namecode PRIMARY KEY
);
INSERT INTO name_types ( namecode ) VALUES ( 'Groceries' );
INSERT INTO name_types ( namecode ) VALUES ( 'Internet' );
INSERT INTO name_types ( namecode ) VALUES ( 'Phone' );
INSERT INTO name_types ( namecode ) VALUES ( 'Cable' );
INSERT INTO name_types ( namecode ) VALUES ( 'Mortgage' );
Next comes my main table. I'll use NAME_TYPES.NAMECODE as a Foreign Key in my main table.
CREATE TABLE t
(
namecode
CONSTRAINT fk_namecode_t REFERENCES name_types( namecode )
CONSTRAINT nn_namecode_t NOT NULL,
date_of DATE DEFAULT SYSDATE
CONSTRAINT nn_dateof_t NOT NULL,
amount NUMBER(16,2) DEFAULT 0
CONSTRAINT nn_amount_t NOT NULL,
date_from DATE DEFAULT SYSDATE
CONSTRAINT nn_datefrom_t NOT NULL,
date_to DATE
);
I'm not worried about a Primary Key as this is for demonstration purposes only.
The DATE_TO field will typically be NULL but will allow me to maintain a history as mentioned above. If I no longer what a record to be displayed, I just "end date" it, or populate DATE_TO with the current date.
To keep it mildly short, I'll use the "Groceries" data for starters.
INSERT INTO t
( namecode,
date_of,
amount )
VALUES
( 'Groceries',
ADD_MONTHS( SYSDATE, -2 ),
250 );
INSERT INTO t
( namecode,
date_of,
amount )
VALUES
( 'Groceries',
ADD_MONTHS( SYSDATE, -1 ),
225 );
INSERT INTO t
( namecode,
date_of,
amount )
VALUES
( 'Groceries',
SYSDATE,
300 );
NAMECODE DATE_OF AMOUNT DATE_FROM DATE_TO
---------- --------- ---------- --------- ---------
Groceries 09-DEC-08 250 09-FEB-09
Groceries 09-JAN-09 225 09-FEB-09
Groceries 09-FEB-09 300 09-FEB-09
If I want to update something, I can write one procedure that should update record I want.
UPDATE t
SET amount = 200
WHERE namecode = p_namecode
AND TRUNC( date_of, 'MONTH' ) = p_dateof;
P_NAMECODE begin 'Groceries' in this case and P_DATEOF being the Year and Month (in DATE format) of the record you want to modify.
That won't satisfy our history requirement though. In order to do that, you'll need to UPDATE the DATE_TO column with the current date and then create a new record. It should look something like this:
UPDATE t
SET date_to = SYSDATE
WHERE TRUNC( date_of, 'MONTH' ) = TRUNC( p_dateof, 'MONTH' )
AND namecode = 'Groceries';
INSERT INTO t
( namecode,
dateof,
amount )
VALUES
( p_namecode,
p_dateof,
p_amount );
Now and queries you write would just need the WHERE date_to IS NULL predicate. If you need history, you just remove the predicate.
SELECT *
FROM
(
SELECT
namecode nc,
TO_CHAR( date_of, 'YYYYMM' ) d,
amount
FROM t
)
PIVOT
(
SUM( amount ) total
FOR d IN ( 200812 AS "200812", 200901 AS "200901", 200902 AS "200902" )
)
(Though I still haven't found a great way to have the columns automatically appear as new dates get added, I'll leave that for a future exercise.) The results look like this:
NC 200812_TOTAL 200901_TOTAL 200902_TOTAL
---------- ------------ ------------ ------------
Groceries 250 225 300
Now you have a easy to maintain, robust design. Hardly any maintenance is required. Queries as as simple as:
