[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

Re: An Introduction to SODA in LedgerSMB



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