Re: An Introduction to SODA in LedgerSMB

["Joshua D. Drake" <..hidden..>]

Chris Travers wrote:
> On Nov 23, 2007 9:53 AM, Joshua D. Drake <..hidden..> wrote:

> > > 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).

Although a correct view it is not a practical view :), which is my 
point. Normalizing out past (really 3rd) 4th form becomes not only a 
serious conceptual problem but an even more serious application problem 
in terms of performance and management.

> 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 do not agree. As someone who deals with normalization issues everyday, 
I am saying, loudly :) that although BCNF through 5NF is *neat* it is 
not something we should "strive" for unless the data model explicitly 
requires it and most do not.

>  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....

Right.


> 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.

Wait I am confused soda.location[] is an array not a compound type...

http://www.postgresql.org/docs/8.1/static/rowtypes.html

In the above example I think what you would actually do is:

comp_typecheck=# create type location as (class text, line_one text);
CREATE TYPE
comp_typecheck=# create type company as (id int, location location);
CREATE TYPE

We want to be careful with this anyway :) It is another of those... wow 
a cool feature but what's the point? Note that a table is nothing but a 
composite type.


Sincerely,

Joshua D. Drake

> Best Wishes,
> Chris Travers
>
> -------------------------------------------------------------------------
> This SF.net email is sponsored by: Microsoft
> Defy all challenges. Microsoft(R) Visual Studio 2005.
> http://clk.atdmt.com/MRT/go/vse0120000070mrt/direct/01/
> _______________________________________________
> Ledger-smb-devel mailing list
> ..hidden..
> https://lists.sourceforge.net/lists/listinfo/ledger-smb-devel