[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
Database schema cleanup
- Subject: Database schema cleanup
- From: Seneca Cunningham <..hidden..>
- Date: Fri, 8 Sep 2006 23:49:05 -0400
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
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
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
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.
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.