NAV Relational database or not?

viriato

One issue I'd like to put up for discussion, is NAV a trully relational database? Do primary keys really represent their tables? What about normalization?

Thanks.

DenSter

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.

viriato

Thanks Denster, I tought so, it's a relational database but if we take for example the "Customer" table, it doesn't seem very normalized. I think this table could be broken down a little more, for example city will end up repeating itself over and over again, don't you agree?


Thanks again.

DenSter

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"?

kine

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.

ritesh.singh

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

DenSter

kine wrote:

It means, priority is quick filtering and data processing, not some theoretical normalization form.

I was trying to let them reach that conclusion themselves

It's just not practical to completely normalize a database. You'd have so many more tables for very obscure reasons, and at some point it becomes too much to deal with. Addresses are basically looked at as a single attributes, and therefore you put all address fields as attributes of other entities. Sometimes, like for Ship-to Addresses, they are stored in separate tables, but still there you wouldn't have a link to a 'City' table.

Actually, there is a Post Code table that has some of that information, but that relationship is not strictly enforced.

viriato

Thanks guys. Maybe it's my impression but i felt some of you felt somehow attacked by this post, which I didn't mean in any way, I was sure there was a reason for doing things like they this.

Thank once again.

Cheers.

DenSter

I don't feel attacked at all

gerdhuebner

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

viriato

Thanks Denster. I just wanted to have that clear . I'm quite new to NAV (been developing in other environments the last years) so I might post some strange posts from time to time. Thanks again.
Thanks gerdhuebner. The city name was just an example, it was exactly about data consistency i was refering too

DenSter

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

DenSter wrote:

...You can call having these fields in multiple places redundant, which it might be depending on your motivation,...

Of course one can call data redundant or not but I think there is a clear definition of data redundancy. I would call data redundant if there is likely no case where the proposed value (City, Description, Sales Price) has ever to be changed away from it's default value. This definition of redundancy is not applicable to the mere database definition. Consider for example two companies using the (functionally) same database (of course with different data). One company may find it necessary to change proposed data (Sales prices, descriptions, etc.) the other not. And of course one cannot say in all cases or in general that the database is the "better", the less redundant data it contains (because there may be other reasons for redundancy like sorting or filtering aspects, e. g.).
On the other hand the "normalization-degree" of a relational database is clearly defined independently of the companies, which are using the database, and is merely based on the data structures and their constraints (relations, etc.) (see http://en.wikipedia.org/wiki/Database_normalization, e.g.). And with respect to normalization it can clearly be stated that the NAV standard application is not fully normalized, but I wonder whether there exists a fully normalized database in productive environments, at all...

DenSter

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.

kine

Filtering, Sorting, History - it is why it is as it is... :-D

History is why the data are copied and not only referred (address on posed invoice must be still same even if the address on the customer was already changed...).

Filtering is, why the data are directly in the tables (filtering on calculated fields is not good...)

Sorting cannot be done without the data directly in the records...

And we cannot use something like joins... ;-)