On Tue, 02 Jan 2001 19:33:50 EST, the world broke into rejoicing as
Eugene Tyurin <[EMAIL PROTECTED]> said:
> On Tue, Jan 02, 2001 at 04:02:24PM -0500, Derek Atkins wrote:
> > David, a good start...
> >
> > David Merrill <[EMAIL PROTECTED]> writes:
> >
> > > We need to determine what level of granularity we want to provide for
> > > user permissions. Here is a simple set of permissions to start with.
> > > Tell me what I've missed:
> > >
> > > - system administration (manage entire system)
> > > - corporate administration (manage one set of books)
> > > - account administration (manage a single account)
> > > - account data entry (add/delete/update records in an account)
> >
> > I would think that add, delete, and update might be split into three
> > different sets of permissions. I may give a secretary permission to
> > add entries, but I dont want him/her to be able to change or even
> > worse delete entries.
>
> Now, this is the dark forest we get into when we start playing
> "enterprise class" database-backed software.
>
> 1. Nothing can be *deleted*. Entries can only be voided or
> superceded, but they have to remain in the database for the
> audit/logging purposes.
Hmmm... If an "entry" is superceded, then we're left with two entries:
a) The original one, which is, in effect, no longer valid, and to which
nothing should, any longer, point.
b) The new one, superseding the original, to which everything should _now_
point.
Unfortunately, this leaves us in danger of being backed into the corner
where changing an entry results in having to cascade changes to all the
database records that refer to the entry.
Which means that when this happens, we really _do_ need to substantively
change the original record.
A sensible procedure would be for the primary key on the table to be
something like a GUID, but _not_ one that is actually used to reference
the transaction "publicly," only to establish a unique primary key.
We then have a _second_ field in the table that is the "public GUID."
When a record is created, a GUID is generated and put in that field.
We also have a _third_ field in the table that is the "original GUID."
Thus, we have, in such a table, fields:
a) prGUID [private GUID] [primary key for the table; unique, not null]
b) GUID [public GUID] [secondary index for the table; unique, nulls allowed]
c) parentGUID [Parent GUID] [another index on the table; NOT unique, nulls forbidden]
When a record gets created, the sequence might be thus:
-> Generate a new private GUID, put it in prGUID
-> Generate a new GUID to be used publicly, put it in field GUID.
-> Also put the second GUID in field parentGUID
When a record is superceded, the procedure might be thus:
-> Generate a new private GUID, put it in prGUID for the _new_ record.
-> Fill in the "rest of the new record."
-> Set field GUID for the new record to have the GUID field value from
the previous version.
-> Set field parentGUID to have the same value found in field GUID.
-> For the record that is being superceded, set field GUID to null, so
that it disappears from the purview of the usual processes that will be
looking for non-null GUID values.
Alternatively, this could fold down to three fields:
a) prGUID [as described above]
b) parentGUID [as described above]
c) status [Active/Superceded]
That cuts the size by one GUID field; might be worthwhile...
It's getting pretty hairy, to be sure. The two extra fields have to be
associated with every table that we might want to keep logged. Probably
as well as fields for:
d) UserID/SessionID of the one that last updated the record, and
e) Date/Time of the last update to the record.
By the way, what with us adding several more fields, and having all
sorts of excuses for there being nearly gratuitous additions of rows
to tables, there probably needs to be:
1) A mechanism for "archiving" obsolete information, so the DB doesn't
grow too tremendously quickly, and
2) A mechanism for turning off logging for those situations where we
plan to modify _hordes_ of records.
At work, the guy I sit beside is periodically responsible for setting
the SAP ERP system to stop doing audit logs, usually because there is
some funky multiple-phase process running on the weekend that will
be updating 40,000 employees several times in order to get all the
changes into place. Gratuitous logging can be a bad thing...
> 2. All database entries (even superceded ones) must be associated
> with the userid and time of creation. This way a user can give a
> date to the program and obtain an exact snapshot of the books.
Another thought: Don't use userid, but rather _session_ ID. Which might
well contain the user ID, mind you...
Consider that an automated process (such as "import the OFX file") might
not have ideal "user" information associated with it, particularly if
it took place late at night when users were snuggled all tight in their
beds, with visions of sugar plums dancing in their heads... [Oops.
Waxing Christmas-like, well after Christmas. Oops.]
--
(concatenate 'string "cbbrowne" "@ntlug.org")
<http://www.hex.net/~cbbrowne/>
I don't suffer from insanity, I enjoy every minute of it!
_______________________________________________
gnucash-devel mailing list
[EMAIL PROTECTED]
http://www.gnumatic.com/cgi-bin/mailman/listinfo/gnucash-devel
