Chris Travers wrote: > On Nov 23, 2007 9:53 AM, Joshua D. Drake <[EMAIL PROTECTED]> 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 > [email protected] > https://lists.sourceforge.net/lists/listinfo/ledger-smb-devel > ------------------------------------------------------------------------- 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 [email protected] https://lists.sourceforge.net/lists/listinfo/ledger-smb-devel
