Database schema cleanup

[Seneca Cunningham <..hidden..>]

The schema that LedgerSMB inherited from SQL-Ledger is rather  
deficient.  I've started to look at the cleanup of it and have seen  
some areas that I believe should be fixed.  I have started looking at  
some of these items, but it would be useful to have the input of others.

Problems that I've seen in the schema:
--------------------------------------

  1) Use of FLOAT instead of NUMERIC
     As Josh Berkus had mentioned on sql-ledger-users, "You can't use
     DOUBLEs for financial figures."  Financial data uses decimal
     arithmetic and quantities rather than binary, so fixing this  
should
     reduce any rounding issues to the usual base-10 complications.
  2) Lack of primary keys
     Primary keys provide a method of accessing any row in a table and
     are desirable in that they allow for proper foreign keys and let
     Slony work.
  3) Use of a pseudo-NULL value
     The value of '0' is used in some places instead of a true NULL
     without a dummy record.  This will not work with proper foreign  
keys
     in place.  My opinion is that these values should be replaced with
     NULLs and proper checks be implemented.
  4) Lack of true foreign keys
     SQL-Ledger uses triggers in some places to fake the use of foreign
     keys.  If primary keys are added and pseudo-NULL NULLified, many
     fields can be converted to being foreign keys.  True foreign keys
     allow the database to enforce data consistency and make the schema
     easier to read and understand.
  6) Lack of constraints
     Most fields have no constraints beyond basic type-checking.   
Many of
     these can be set to NOT NULL.
  7) Specification of WITH OIDS
     Use of OIDS is deprecated.  Fixing this is not currently critical,
     but any piece of code that relies upon OIDS should be tracked down
     and changed to using the appropriate primary key instead.

--
Seneca Cunningham
..hidden..