Re: An Introduction to SODA in LedgerSMB

["Chris Travers" <..hidden..>]

On Nov 23, 2007 9:53 AM, Joshua D. Drake <..hidden..> wrote:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> On Thu, 22 Nov 2007 20:06:55 -0800
> "Chris Travers" <..hidden..> wrote:
>
> > With the added participation in the development of LedgerSMB, there is
> > a greater need for understanding of our architecture and anticipated
> > code structure.  Furthermore, some of the structures have evolved in
> > the process of development, and additional clarification is necessary.
> >
> > 1:  The database should be designed independently of the application
> > at first.  All fields in the database should be semantically atomic
> > and should be broken out as much as possible.  Additionally, we should
> > be searching for additional opportunities to further normalize the
> > database to either BCNF or 5NF.
>
> I will be hard pressed to provide any support around the idea of 5NF.
> Except in the most rare and loudly argued, proven and documented
> practical benefit thereof. /me has yet to see a practical
> implementation of 5NF that is anything more than mental masturbation.

Ok, I think we may be suffering from a lot of general issues of
normalization discussions.  My understanding of BCNF->4NF->5NF is
based on mathematical definitions rather than applications or
misapplications of popular definitions.  My view of normalization is
that it is a mathematical rather than a conceptual process (i.e. one
works in terms of domains and dependencies rather than in terms of
logical groups of attributes).

I think that the key is to suggest that BCNF through 5NF ought to be
acceptable normal forms for this project, and we ought to, where
appropriate, push individual relations as far along that progression
as makes semantic sense for the data involved.  I am not saying 5NF
for 5NF sake.  I am saying BCNF as a minimum and then, if appropriate
pushing specific relations towards 5NF as far as it makes sense.
>
> You are also introducing scalability considerations far outside the
> scope of the average LedgerSMB user when you start trying to move
> beyind 4NF.

There is also the issue that some data cannot be practically
normalized beyond BCNF without introducing artificial complexity.  For
example, the storage of journal line items....

>
> > Proposed Future Enhancements longer-term:
> > Long-run I would like to see more effort made at rich data structures
> > as discoverable entities.  This probably means developing conventions
> > which allow for custom types which include, as attributes, arrays of
> > other custom types.  These issues pose substantial technical changes,
> > and cannot happen anyway until we are ready to drop support for
> > PostgreSQL 8.1.  However, it is an area I would suggest we start
>
> What is it about 8.1 that is limiting you here?

Arrays of complex types are not implemented, thus ensuring that
complex data structures for both input and output are not
automatically discoverable

postgres=# create type soda.location as (
postgres(# class text,
postgres(# line_one text,
postgres(# line_two text,
postgres(# line_three text,
postgres(# city text,
postgres(# state text,
postgres(# country text
postgres(# );
CREATE TYPE

postgres=# create type soda.company as (
postgres(# id int,
postgres(# entity_id int,
postgres(# locations soda.location[],
postgres(# legal_name text
postgres(# );

ERROR:  type "soda.location[]" does not exist

This drastically reduces the ability to create automatically
discoverable complex data types.

Best Wishes,
Chris Travers