Designing a C/SIDE Application
2.1 I NTRODUCTION TO C/SIDE A PPLICATION D ESIGN
In this section we will briefly outline the procedures involved in designing a C/SIDE database application. It usually includes the following steps:
Understanding the Problem Make sure you understand the business problem you are trying to solve. Be sure you know who will be using the application and what they will be trying to accomplish.
Designing the Tables Begin by designing a data model that you use to determine how the data will be stored and how it can be most meaningfully utilized. The data model determines:
· which tables the database must contain.
· what kind of data you want to store in the fields in the tables.
· how the data in the tables are related to each other.
· constraints that are necessary to ensure data integrity.
Designing the Application When you have completed the design of the database tables, you are ready to begin designing the application itself. This involves:
· designing forms (to enter and retrieve data) and reports (to retrieve and present data).
· creating C/AL code to connect the application objects.
The above steps depend on each other. When you go from one step to another you will often have to rethink some of the decisions you made in the previous step.
Understanding the Problem
To decide which information you should store in C/SIDE, you have to determine the purpose of the database and how it will be used. The easiest way to do this is to talk to the people who will use it. Involving the end user as early as possible eliminates problems that can stem from misunderstandings about the purpose of the database.
Interviewing the end users will help you get a better understanding of the tasks they expect the system to be able to solve. Based on this, you can determine the data (tables) necessary for completing these tasks. This will often be the most difficult part of the design processand also the most important, as the usefulness of the entire application depends on whether the tables have been designed correctly.
Your interviews of the end users will give you a good knowledge of which questions the end users want answered and thus of the information that forms and reports should provide. This does not necessarily tell you how you should structure your tables, however.
2.1 Introduction to C/SIDE Application Design
Designing the Tables
Your next task is to divide the information you want to store in the database into basic categories such as customers, products, employees, and so on.
You begin by defining a data model. This model should describe:
· the tables in the database.
· the fields in the tables.
· the relations between the fields in your tables.
· constraints for fields and relations.
A model suitable for this purpose is the ER model (Entity-Relationship model). An ER model is capable of mapping real-world situations to a relational database system such as C/SIDE.
Basically, an ER model divides all the elements of a real world situation into two categories: entities and relations. An entity is a "thing" in the real world with an independent existence. An entity may be an object with a physical existence, such as a particular car or person, or it may be an object with a conceptual existence, such as a company or a job. Relationships describe how the entities are related.
To use the ER model, you will complete the following steps:
1 Identify the types of entities associated with your problem. Create tables to represent each of these types of entities.
2 Identify the properties of each entity type and create fields in the tables to represent each of these properties.
3 Identify the relationships between the entities and add these relationships to the tables.
The following subsections are not intended to serve as a description of all facets and implications of the ER model but are rather intended to give you an overview of the model and at the same time show you the benefits of applying a formalized design method.
How Are ER Model Concepts Related to C/SIDE Concepts?
A real world problem will usually contain groups of entity types that are similar. For example, consider a company having hundreds of customers. All of the customers are entities. These customer entities share the same properties, but each entity will have its own values for the properties. Such similar entities define an entity type, that is, a
The following table summarizes how basic ER model concepts relate to C/SIDE concepts.
Determining Field Types
In the ER model, after you have identified the entity types and their properties, your next step is to determine the types of values these properties can have. In C/SIDE this corresponds to determining the data types of the fields in your tables.
EXAMPLE
Suppose that your analysis using the ER model has revealed that you have an entity type describing your company’s customers. This has led you to define a Customer table:
Your analysis has shown that you need fields such as Company Name, Contact Person, Phone and Payment Method. When you implement the Customer table, you select the following field types:
Refer to Choosing Data Types on page 23 for a description of the C/SIDE field types.
Role of Keys in C/SIDE
The ER model places a very important constraint on the entities in an entity type (records in a table). This is the key or uniqueness constraint on the properties (fields).
An entity type usually has at least one property whose values are distinct for each
ER Model Concept Corresponding Concept in C/SIDE
An entity type A table
An entity A record
A property A field
Field Name Description Field Type
Company Name This field is used to store the name of the customer (for example, "Microsoft Business Solutions ApS").
string
Contact Person This field identifies the contact person in the company (for example, "JLJ").
string
Phone This field contains the customer’s phone number (for example, "45662111").
string
Payment Method This field describes the payment method for the customer (for example, "pay in cash").
2.1 Introduction to C/SIDE Application Design
individual entity. The table below shows how the ER model concepts are related to C/SIDE concepts.
For C/SIDE to be able to operate efficiently on the data in tables, the records must be arranged according to some criterion (that is, a key). For example, an Employee table can be ordered according to the employees’ social security numbers because this number uniquely identifies each employee.
In order for a field to be a key for a table, the uniqueness constraint above must hold for every record in the table. This constraint prevents any two records from having the same value for the key field. It is not a constraint on a specific record but a constraint for all records in the table, considered together.
Sometimes a key consists of several fields together; in this case the combination of the field values must be distinct for each record.
Sometimes you will be able to define several keys for a table. Refer to the section How to Define a Primary Key on page 26, which discusses the concepts of keys.
Determining the Relationships
At this point in the design process, you have carefully planned a number of tables to store individual types of information. In your final application you want to be able to retrieve the information in a meaningful way. Very often an answer from your database will consist of information stored in several tables. To allow for such answers, C/SIDE uses relationships to chain related information together. In database terminology it is common to distinguish between three types of relationships:
One-to-Many Relationships In this type of relationship, a record in Table 1 can have more than one matching record in Table 2, while a record in Table 2 can have no more than one matching record in Table 1. This is the most common type of
relationship in a relational database.
Many-to-Many Relationships In this type of relationship, a record in Table 1 can have more than one matching record in Table 2, and a record in Table 2 can have more than one matching record in Table 1. This represents a problem in database design and may signal an inefficient design. Normally you break down a many-to-many relationship into two one-to-many-to-many relationships.
ER-Model Concept Corresponding Concept in C/SIDE
Constraints on the entities of an entity type Constraints on the records in a table The uniqueness constraint on entity properties A key based on fields in a table
Assuring the Quality of the Design
In the process of defining the tables and setting up relationships, you will often have to select from among several possible solutions. To make sure that you select the most appropriate solutions, you need a way to measure design quality.
This is done using what is known as the normalization process. The normalization process takes your design through a series of tests to verify whether it belongs to a certain normal form. There are six normal forms. Most texts on relational database design can teach you how to obtain these normal forms. Some good starting points are the books mentioned on the last page of this chapter.
Designing the Application
After you have completed your table design, you are ready to begin designing the application itself. From the analysis phase, you have an overview of which answers the application is expected to be able to provide. From the table design phase, you have a clear description of where and how the information will be stored. Based on this understanding, you are ready to begin assembling the entire application.
This part of the application design involves:
Creating Forms Forms are used to present or collect information. You have access to a number of design elements, such as text, data, pictures, lines, and color.
Creating Reports Reports are used to present data as printed documents. Reports allow more flexibility than forms do when you want to present summary information.
Creating C/AL Codeunits Codeunits are containers for storing C/AL code. When you put the code into a codeunit, you can reuse the same algorithms many places in your application. This reduces the size of the application and makes it easier to maintain.
Testing and Refining the Application Before you release your application, you have to analyze your design for errors. This is normally an iterative process.
At this point you will have a useful application. If you took the time to plan all steps of the application design carefully, you also have an application that is fully documented.
This will prove to be a great help when you need to make future adjustments and additions.
Recommended Books on Database Design
Some of the most well-known books about relational database design are:
C. J. Date. An Introduction to Database Systems. Addison-Wesley Publishing Co.
Elmasri, R. A. and Navate, S. B. Fundamentals of Database Systems.
Benjamin/Cummings.
Dutka, A. F. and Hanson, H. H. Fundamentals of Data Normalization. Addison-Wesley Publishing Co.