On Wed, Oct 19, 2011 at 4:48 AM, Chris Travers <[email protected]> wrote: > This is probably slightly off-topic, but here's what we did in > LedgerSMB 1.3 to address this problem. It would be a little more > complicated in SQL-Ledger but probably someone could do it without any > real difficulty as the same subset of information would be of > interest. > > Basically you can create a table whose sole purpose is to maintain > referential integrity. > > CREATE TABLE transactions (id int, table_name string); > > table_name is kind of optional but it makes > > Then you can add triggers to maintain rows in the transactions table > from (at least, depending on how far you want to take this): > ar > ap > gl > > Triggers should work on insert, update, and delete. > > Then you can add foreign keys against that table, something like: > alter table ar add foreign key (id) references transactions(id); > alter table gl add foreign key (id) references transactions(id); > alter table ap add foreign key (id) references transactions(id); > alter table acc_trans add foreign key (trans_id) references transactions (id); > > Then you can be sure three things: > 1) When you insert data it will connect in a meaningful way, and > 2) You can't update the id field or delete the record in a way that > leaves acc_trans records orphanned. > 3) ID's can't be re-used between these tables. > > I'd be happy to post code if folks are interested. > > One note: > You can't use rules instead of triggers. We learned this the hard way > in 1.2 :-)
Hi Chris, Thanks for the tip. Looks good. sql-ledger database does require clean up at various places. On the other hand, orphaned rows issue seems to be rare to me now (during support queries). If it is there it is always in the past years data and not in recent years (2010, 2011). So probably sql-ledger code base (and all forks based upon it) is getting bit more stable. :-) SQL-Ledger has probably the largest installed base and not every body is willing to upgrade to the latest (or to Ledger123) unless there are orphaned rows. :-) LedgerDoctor was written to quickly detect and correct referential integrity issues (as well as few other bugs) and it worked without any database change. I always advice to run it at least once a day. (it takes only a single click) Regards Armaghan _______________________________________________ SQL-Ledger mailing list [email protected] http://lists.ledger123.com/mailman/listinfo/sql-ledger
