Skip to content

Understanding Database Management in Dynamics NAV

To start with the basics, a database is simply a collection of data or records stored in a computer system. A database management system (DBMS) is an application program used to create, process, and administer a database. Examples include Microsoft SQL Server, IBM DB2, and Oracle Database 12c.

There are several different ways to represent your database (e.g., database models), but relational databases are the most commonly used model. A relational database represents data as two-dimensional tables, called relations. As a specific example of how relations work, rows might represent entities (e.g., a supplier) and the columns would represent the various attributes of the entity (e.g., address, email, telephone number, etc.).

That may sound simple enough, but what makes relational databases awesome (and perhaps a bit confusing for people) are primary and foreign keys. Each are explained in detail below:

A primary key is a column that identifies a unique row in a table (e.g., Customer Number). A primary key is essential to maintaining an organized database. In Microsoft Dynamics NAV, for example, the customer list table gives each customer a unique customer ID number, which serves as the primary key. The primary key from the customer list table is used as a foreign key in the sales order list table. Therefore, a foreign key is a key from a different table that represents a set of information. See Tables 2.1 and 2.2 below for an illustrated example. Overall though, primary and foreign keys are the foundations to relational databases.

Maintaining an organized database requires data normalization. Normalization means eliminating duplicated and repetitive data in a database. The goal is to have one relation for each table. The example below shows a denormalized table vs. a normalized table.

Table 1: Denormalized Table

Sales Order No.

Customer No.

Customer Name

Customer Address

Item No.

Item Name

Description

Qty

 

Unit of Measure

Price

Normalized Tables

Table 2.1: Sales Order List

Sales Order No. (Primary Key)

Customer No. (Foreign Key)

Item No. (Foreign Key)

Qty

Price

Table 2.2: Customer List

Customer No. (Primary Key)

Customer Name

Customer Address

Table 2.3: Item List

Item No. (Primary Key)

Item Name

Description

Unit of Measure

Having normalized tables reduces the room for human error because fields are not being filled out multiple times. The example above was relatively simple, but enterprise level database issues can cripple performance. As a whole, companies shouldn’t undervalue the importance of a well-managed database.

For more information on database management, contact the experts at ArcherPoint. For more blogs on development topics, check out our collection of NAV Development Blogs. 

Blog Tags: 
Read ArcherPoint's Blog Follow us on Twitter Follow us on Facebook Follow us on LinkedIn Link to our RSS feed Join us on Google+ Watch us on YouTube
Get Help Now