I would like to start the subject about all of that enourmous data redundancy in NAV (redundant fields, tables, etc.).
I’ve started to work with NAV 4 months ago and one of the first things I disliked about it was a lack of database normalisation principles. Everyone who studied relational databases a bit more should understand what i’m talking about. I would like to hear opinions from all of you experienced guys (and maybe women, if there are any), why are these principles so neglected. Don’t tell me it’s because of the performance issuess! Computers nowadays are quite fast machines and will only get faster and the development and maintenance would be quite easier without all of that redundancy. I’m saying this, because performance reasons are the first I was told about when I’ve started with this work.
So, if anyone is willing, please share your opinions and knowledge with me…
Well, normalisation should exclude replication of data but replication is needed cause of technical solution of filters in NAV. Filters are all over NAV and are making a major benefit. Now, is this the best way (technically speaking) for this kind of benefit, that can be a subject to discuss…
Btw. a lot of replication data is not really replicated data (cause, it is not updated upon update of primary record), but rather historical data. Storage is cheap (and getting even more) but handling so much data is not. I think the growth of disk sizes is not followed by growth of disk access speeds (r/w).
The vendor infomation is in the Purchase Header for historical reasons. This is what gets moved to the Posted Receipt and Posted Invoice. If I reprint an old Receipt or Invoice, I want the original vendor info not the updated info.
Navision fulfilled 2NF database normalisation, which is the standards I assume you are talking about.
What happens if you want to sell something to someone and just this once they want it shipped to a different address? Its easier to modify the data in the PO header than go and create a whole new shipping address record.
Much of what appears to be de-normalization is there for business process reasons. Example: Both the Sales Header and Sales Lines table have a “Location Code” field. Navision supports orders that ship from multiple locations. The “Location Code” in the Sales header is simply a default. The system looks at the sales line when posting.
As you must now there are 3NF, Boyce-Codd’s NF, etc. I took table 38 just as an example and I understand that there are historical reasons for not updating some data, but those are exceptions, what about other cases?
You could easily write an UpdateSalesHeaderCustInfo() in Customer table or UpdatePurchHeaderVendInfo() in Vendor table and propagate the change to only Sales or Purchase Header when the non-key fields are update in the Customer or Vendor table (put this in the OnModify trigger). This is what we did in our NAV implementation and it saves our users hours from having to update Sales/Purchase Orders manually whenever the Customer/Vendor Card is updated. You can do the same to Ship-to Address or Location table too if you wish. However you don’t really want to do this to any Posted Documents even though it is technically feasible.