Tables are the fundamental objects in any database. They are the objects that uses for storing and manipulating the data. Table designs play and important role in efficient application development. When creating tables in Microsoft Dynamics Nav following are practices that we make for efficient development.
- Before creating the table always create the ER-Diagram. State all the fields, data types and relation with other tables. You can use Microsoft Visio for any other ER designing tool for this. To avoid anomalies in database design it is very important.
- Before modification to any object always take its backup.
- Numbering conventions that are used in the base application and guidelines for objects and fields that are outside the base application are described. Use the numbers from 50,000 – 98,999 for table objects. Object no. 50,000 – 98,999 belongs to customer design area.
- Do not use the object numbers 99,000 – 99,999 for objects that you create, even though they are in the customer design area. The training material for Microsoft Dynamics Nav uses these numbers.
- It is better to assign the sequentially related number to the likewise objects.For Example:If I am creating 3 objects for implementing some sales scenario I will assign the ID 50001 object 1, 50002 object 2, 50003 object 3.
- For the fields assign no. from 50,000 – 99,999 i.e. Customer design area.
- When adding a new field in Dynamics Nav table tables numbered between 1 and 49,999 do not use the field numbers 99,000–99,999.The training material for Dynamics Nav uses these numbers.
- When creating a new an independent table don’t leave gap between field numbers.
- Table names should always be singular. A table containing data about customers should not be named Items, instead it should be named Item.
- Table name and field name should be descriptive but within reasonable length.
- Always name a table such that it is easy to identify the relationship between the table and the data it contains. For Example two tables containing the transactions on which a document page is based should normally be referred to as a Header table (for the main portion of the page) and a Line table (for the line detail portion of the page).
- Always define the delete action on onDelete() trigger as per your business logic. For example: if you are deleting a record from header table define the delete logic to delete lines from its related line table so that you don’t have any un-referenced line in your system.
- Every change to an object should be briefly documented in the Documentation The use of a standard format for such entries makes it easier to create them as well as to understand them two years later. There should be a sequential list of changes showing each incremental version followed by a list of all the features implemented for that version. This approach provides a standardized Version list externally and the full detail of changes internally. The structure of the documentation tag would be
CD <document no>.<Version no> – <date> – <organization\individual name> – <change description>
CD 02.00 – 2015-03-16 – Axpulse – Change to track when new customer added
- Added field 50012 “Start Date”
CD.03.00 -2017-02-16 – Axpulse – Change to increase comment length
- Field 22 “Description” length changed 50 to 90
- In table designer, describe table relations for related field in description.
- In table designer, describe flow field and flow filters in description.
- In table designer, describe options for option type field in description.
- The last is that when create or modify any object always update the version tag. The version tag represent who have last modified this object and for which version of Dynamics Nav.
AXP is our initials and 8.00 is the version of Dynamics Nav in which we have modified the object.
Maintaining this practice combined with good external documentation describing the reasons and intended outcomes of each modification, the result is a system that is much easier to maintain.