NAV Relational database or not?

Yes NAV is built on a relational database, both in its proprietary database system as well as on SQL Server. You could argue the extent in which certain areas are normalized, but I don't think the fact that it is in fact a relational database is up for any discussion. The primary key of every table in NAV uniquely identifies its records, if that is what you are asking.

What's your point? Is City not an attribute of the Customer entity in your opinion? Where does it get repeated over and over again? How would you "break it down further"?

I feel that it is so to ease the reporting part of the product, as end user is authorised to create reports.

kine wrote:

Some data in NAV are "repeated" to allow users quickly and easily filter on the data.Because if the field is in another table that you are on, you cannot filter on it. It means e.g. if there will be only post code on the customer, you cannot filter the customers on city and you need first find all post codes for the city and than filter on these codes. It is user un-friendly.
It means, priority is quick filtering and data processing, not some theoretical normalization form.

Well, I think the problem of filtering can be solved in practice by defining a LookUp-FlowField for City - may be the time for filtering will increase for a big list of Customers... - But the main problem is sorting. Assume, you want to sort your Customer List alphabetically by City. This cannot be achieved with a FlowField, because one cannot define a key containing FlowFields. In a purely SQL oriented environment the required sorted list could be achieved by defining a joined view. In NAV you have to use a key of the table for sorting. This is one reason (and may be the main reason) why you can find "double data holding" at one place or annother in NAV. The example of the field "City" in the table Customer is not problematic, since the city name for a given post code may hardly ever change. More problems can arise, for example, with the field "Description" in the "BOM Component" table. When the description of an item component is changed, the description in the "BOM Component" table is not changed automatically... - in that case, it would be better to use a Lookup-FlowField, but then you could not provide the BOM Component line with an individual description any more... - things are not as easy as they look at first glance...
There is yet annother aspect concerning relational issues in NAV. You may have noticed already, but you can delete a Post Code record from the "Post Code" table, though this post code may be used by some customers. There is no "default" security mechanism in NAV which could prevent you from "breaking" relations in this way. The only possibility would be, to put some code into the OnDelete trigger of the "Post Code" table... - on the other hand, because of the fact, that the City is a true field in the customer table, the deletion of the "Post Code" record is not too tragical...

Neither the manner in which constraints are enforced, nor the extent in which the data that it contains is normalized, determines whether a database is relational is not. What makes a database relational is having the ability to define relationships between entities within the database.

Having a Description field in the BOM tables is simply there to provide the user with a place to enter any description, without dictating what exactly that description ought to be. It simply defaults to the description of the entity that is selected in there, but it is ultimately up to the user what to type in there, without affecting the description of the entity itself. The same thing applies to prices. The price on the order is the final price, you should be able to modify that per order, without affecting the price of the entity that the order is linked to itself. These fields default in by way of the validation code in the tables, NOT because of their relationship. The relationship is limited to the foreign key into the other table.

You can call having these fields in multiple places redundant, which it might be depending on your motivation, but it always serves a very definite functional purpose. In itself it does not change the fact that the database is relational.

gerdhuebner wrote:

a whole bunch of stuff

Gerd, you are making exactly the same points that I am making. Glad that you agree with me

The degree in which the NAV database is normalized depends on functional requirements. Just because there is a description field in both the Item table and the Sales Line table, does not necessarily mean that it is redundant. It only means that it is redundant, if your functional requirement is that those two descriptions must ALWAYS have the same values. Only in that case would you not add a description field to the sales line table. Another reason why it would be in there is performance. It is much faster to read the description from the sales line when you're already in there, than it is to get the descriptions from the item table, the resource table, the G/L Account table, etcetera.

You could make a case that the description field needs to be removed from the sales line table in favor of a linked field to the other tables, but then you would not be able to modify the description on the sales line, which is a functional requirement in standard NAV. The functional requirement causes the field to not be redundant. In reality 99% of the time it will store the same values though, so I understand that there's a case for it.