NAME 200812 200901 200902The table structure for that would look like the following:
--------- ---------- ---------- ----------
Cable 75 75 75
Groceries 250 225 300
Internet 50 50 50
Mortgage 1000 1000 1000
Phone 25 25 25
CREATE TABLE tIs 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 tThat'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.
SET "200901" = 400
WHERE name = 'Groceries';
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)
4. History Table
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_typesNext comes my main table. I'll use NAME_TYPES.NAMECODE as a Foreign Key in my main table.
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' );
CREATE TABLE tI'm not worried about a Primary Key as this is for demonstration purposes only.
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,
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 tIf I want to update something, I can write one procedure that should update record I want.
ADD_MONTHS( SYSDATE, -2 ),
INSERT INTO t
ADD_MONTHS( SYSDATE, -1 ),
INSERT INTO t
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
UPDATE tP_NAMECODE begin 'Groceries' in this case and P_DATEOF being the Year and Month (in DATE format) of the record you want to modify.
SET amount = 200
WHERE namecode = p_namecode
AND TRUNC( date_of, 'MONTH' ) = p_dateof;
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 tNow and queries you write would just need the WHERE date_to IS NULL predicate. If you need history, you just remove the predicate.
SET date_to = SYSDATE
WHERE TRUNC( date_of, 'MONTH' ) = TRUNC( p_dateof, 'MONTH' )
AND namecode = 'Groceries';
INSERT INTO t
SELECT *(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:
TO_CHAR( date_of, 'YYYYMM' ) d,
SUM( amount ) total
FOR d IN ( 200812 AS "200812", 200901 AS "200901", 200902 AS "200902" )
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: