[Leaplist] coding - rdbms, revisions, and fks

[John Simpson]

On 2009-11-30, at 1258, Jason Boxman wrote:
> 
> An excellent approach that naturally lends itself to managing invoices.  I suppose the solution naturally follows the described problem.
> 
> A better example of the issue is, let's say I have four tables:
> 
> people, addresses, campaigns, mailings.
> 
> A person can belong to a campaign via a mailing many times.  Each time, a mailing would be sent to an address, of which a person can have many addresses.
> 
> If a person or address is ever removed that is a part of a mailing, the integrity of the whole thing blows up.  Historically, at least, 2 years after a mailing, if a person is removed, the accurate count of recipients is wrong.  That might effect the calculated response rate, return rate, and so forth in some report of historical data.
> 
> So some mechanism must exist to handle such a situation.

as you do the mailings, add a record to a history table which contains snapshots of whatever data you might need in the future.

what you're really after is a permanent record: something electronic which serves the same purpose as a file cabinet full of paper, but without the file cabinets or paper. and you need to be able to submit custom queries against it, because you don't know what kinds of questions you're going to need to answer in the future.

to me that sounds like a job for a new SQL table. for the sake of this discussion, let's call the new table "history".

since "people" and "addresses" are the things which might be changed or removed over time, keep a record of what their values were at the time you used them by copying the full contents of every field in those records into your "history" table, as you actually process the lists.

i'm assuming this is a normal direct-mail system, where a single campaign might consist of multiple mailings, each person might have multiple addresses, and each piece mailed is sent to one specific address. your existing schema will look something like this:

people:
	pid	int4		not null primary key
	name	varchar(40)	not null

addresses:
	aid	int4		not null primary key
	pid	int4		not null references people ( pid )
	addr1	varchar(40)	not null
	addr2	varchar(40)
	city	varchar(30)	not null
	state	char(2)		not null
	zip	char(9)		not null

campaigns:
	cid	int4		not null primary key
	descr	varchar(40)	not null

mailings:
	mid	int4		not null primary key
	cid	int4		not null references campaigns ( cid )
	cseq	int4		not null
	started	timestamp
	ended	timestamp
	count	int4		not null default 0

the "history" table would look something like this...

history:
	hid	sequence	not null primary key
	sent	timestamp	not null default now

	mid	int4		not null references mailings ( mid )
	mseq	int4		not null

	pid	int4		not null
	name	varchar(40)	not null

	aid	int4		not null
	pid	int4		not null
	addr1	varchar(40)	not null
	addr2	varchar(40)
	city	varchar(30)	not null
	state	char(2)		not null
	zip	char(9)		not null

as you process the mail pieces, you execute "INSERT INTO history" queries which contain the mid (mailing id you're processing) and mseq (sequence of this piece within the processing of that mailing, which your program will calculate in memory as you go along) as well as the full contents of the "people" and "messages" records you're using to get the data for the label.


> And yet old information needs to be hidden from view, for example.  It's kind of like reference counting garbage collection.  A person can't go away via DELETE if there's any existing reference, or bad stuff happens.

if the historical data is in a separate "history" table, then it cannot and will not show up in your existing queries which don't use that table. this makes it about as "hidden from view" as you can get.

you can (and probably should) make the history table table "write only" by using GRANT and REVOKE to ensure that the SQL user which the app uses, does not have UPDATE or DELETE permission on the table.

REVOKE ALL ON history FROM xyz
GRANT SELECT,INSERT ON history TO xyz

you may need to adjust the syntax for whatever SQL server you're using, but i'm sure you see the idea.


> The more complicated case is, if an address is changed, maybe the street is changed, because a mailing came back, and there's a report that tracks returned mails.  The output of the report is now incorrect.  So then can an address never be changed if it is assigned to a mailing?

the address in the "history" record cannot be changed. after all, you can't change the fact that you did actually send a mail piece to that address.

however, you can change the address in the "live" tables any time you like. the historical record won't be changed at all.

the upshot is this: your reporting programs are going to have to be changed to use the history table. however, that can be a good thing. not only can your report show what the address was that you actually sent it to, but you can compare that to the "live" records and tell whether or not the record has been changed since.


> Or in that case, do you have a mailings_addresses table and simply copy it at that time, so the original address can be changed or deleted as necessary in the future as clerical needs demand.

which is basically the same thing i described above.


> In my case, I am implementing an is_deleted column for managing deleted records, perhaps disallowing DELETE unless there's some big fat warning and a specific level of granted access.

that's also a good idea, for a few reasons:

- it allows you to remove records from processing

- it allows you to "un-delete" records if needed

you could also only GRANT SELECT,INSERT,UPDATE on the tables for the app's SQL user, and if you ever need to DELETE anything you would either have to do it manually, or go into a special administration area (or a different app) which logs into the SQL server using a different userid, and is therefore allowed to execute DELETE queries in those tables.


----------------------------------------------------------------
| John M. Simpson    ---   KG4ZOW   ---    Programmer At Large |
| http://www.jms1.net/                         <jms1 at jms1.net> |
----------------------------------------------------------------
| http://video.google.com/videoplay?docid=-1656880303867390173 |
----------------------------------------------------------------





-------------- next part --------------
A non-text attachment was scrubbed...
Name: PGP.sig
Type: application/pgp-signature
Size: 194 bytes
Desc: This is a digitally signed message part
Url : http://lists.leap-cf.org/pipermail/leaplist/attachments/20091203/87ec09de/PGP.bin