Relatioanal Database Normalisation and Data Redundancy in NAV

Hello everyone!

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…

Hey,

Thanks for starting a new thread, it helps identify exactly what were talking about.

Why do you think Navision does not follow normlisation rules? Its a relational database after all, and I am struggling to think where data is replicated over and over other than the standard FK links.

/TH

Are you kidding?! Take for example Table 38 Purchase Header. Consider all those data about vendor. Let’s say that you change vendors name in Vendor table, will it be changed in related tables too?

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

O.T.
Srđane, pozdrav iz Sarajeva :slight_smile:

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.

/TH

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.

:)))) Đe si zemooooo!!! Nećeš vjerovati, ali ja sam inače rodom iz Sarajeva! Živio tamo do rata, a poslije… Jebiga…

All forum posts are to be in English except in region specific areas of this site please

/TH

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?

Sorry, the guy is from my home town so we were just saying hi…

Actaully there is up to 5NF, but regardless Navision is a 2NF normalised database. So I am little unsure as to what you are getting at about it not being normalised.

  • Eliminate duplicative columns from the same table.
  • Create separate tables for each group of related data and identify each row with a unique column or set of columns (the primary key).
  • Remove subsets of data that apply to multiple rows of a table and place them in separate tables.
  • Create relationships between these new tables and their predecessors through the use of foreign keys.

That’s all there is to a 2NF database. Which means its normalised.

/TH

…And in many cases I want some fields to be updated and I have to do it manually…

Having any other data except primary key as a foreign key in related tables is considered unnormalised! Isn’t it?

No it’s not, that’s 3NF.

  • Remove columns that are not dependent upon the primary key.
    /TH

It sure can…

Yes, ofcourse, and as you say NAV is normalised up to 2NF. All I’m saying is that I think it would be better that it goes up to 3NF. Don’t you agree?

The only area which I have seen, which does not confirm to normalization rules and has caused us immense problem is in the “Drop Shipment”/”Special Order” area of a sales line.

On the sales line table you have

Special Order

Special Order Purchase No.

Special Order Purch. Line No.

Drop Shipment

Purchase Order No.

Purch. Order Line No.

Why there is not an indicator as to what type of relationship there is, I do not understand. This would normalize the structure to

Relationship Type

Purchase Order No.

Purch. Order Line No.

As a sales lines can not be a special and drop at the same time ( to my knowledge )

As we use both special and drop shipments all our sql/reports which link from so to po have to have additional clauses to allow for both cases – very annoying!!

Does anyone know if this has been address in a latter version of Navision, we work on 4.0.? Or does anyone know of a business case where this structure is needed?

Andy

Hi there,

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.

UpdateSalesHeaderCustInfo(Rec)

{

WITH SalesHeader DO BEGIN

RESET;

SETCURRENTKEY(“Pick a relevant Sort Order”);

SETRANGE(“Sell-to Customer No.”,Rec.“No.”);

SETRANGE(“Document Type”,“Document Type”::Order);

IF FINDFIRST THEN BEGIN

REPEAT

VALIDATE(“Sell-to Customer No.”,Rec.“No.”);

MODIFY;

UNTIL NEXT = 0;

END:

END;

}