Hi
Most of the database design work is being done by Josh Drake, and he is an expert in such things and if he doesn't do it, I am sure he will provide some strong feedback on the data model. One of our major tasks is making the database design follow the relational model properly. This means proper normalization and data design. We are aware that relational databases are math-based constructs, and should not be approached from the perspective of the program logic(except for the stored procedures).
Can I respectfully suggest that you missed the point that David was making?Sybase wrote some great DB design books that cover this stuff really well and no doubt there are other books. But basically:
1) Take a step back and build a "logical" model of how the system works and all the various datatypes that are required. In order words to the frontend data design! (kind of) 2) Then figure out how to map what you need into "physical" database tables. For example it may make sense to put a bunch of lookups into a single table called lookups, or it might make sense to have separate tables... Likewise normalisation in some places may make sense, but not in others. Multiple logical concepts may map to a single physical table
The point is that whilst normalisation is good, it needs to be done carefully and with respect to a mapping to the data that is actually desired
Also although I have only built a few accounting systems in the past, generally we found that full normalisation is not usually a good goal to strive for because of the sheer number of records which are required. Unless you have very strong ordered indexes you end up with massive amounts of table scans and performance may not be suitable
However, certainly I built one small system around the concept of a massive table of transactions and the whole accounting system got driven from that. Not sure how it would scale in practice though..? Certainly it's conceptually appealing though
One area though that I think is likely to drop out is that sales orders, sales invoices and sales quotes are all special cases of the same object. And hence it will likely be appealing to map them to the same physical objects. I think this is worth thinking about some more because this is likely to be an important decision affecting workflow later
Cheers Ed W