Defining data schema for your application

This article will provide you with a general overview of how to define the data schema for your application from within the Morfik development environment. It is presumed that the user already has some familiarity with the basic concepts of relational databases or perhaps some experience with a generally available DBMS such as Microsoft Access, Microsoft SQL Server, Oracle, Firebird or MySQL; however, you will find supporting articles within this documentation.

The focus for this article will be on providing a quick reference for how to use the built-in tools and features of the Morfik development environment to construct a relational model for the complete application that we will build throughout the database-related articles of this guide.

Morfik comes with a built-in database which is a full featured database server. This open source database called Firebird can be freely deployed with no licensing costs. Morfik simplifies the use of this powerful database server, making it very easy to use. This allows developers to benefit from the power of a full relational database server while having the ease-of-use of a desktop database.


Database schema or model

Morfik has built into the Morfik development environment the concept of your database model, represented by the Relationship View, where you can add your tables and visually define the relationships. It is important to have in mind that the Relationship View only shows the tables that have been explicitly added to it and so might not represent the totality of the elements that exist in the project’s database.

All tables in a Morfik Project can be seen in the Project View. Tables can be hidden, and normally will not appear on the Table View. This can, however, be configured through the Project Options dialog which can be called up by selecting Options under the Project menu.

A good database model is important in order to create a well planned application. This is especially true in a Morfik application as a well-planned database will make it easier to create the application’s interface.

Note: If you have never worked in depth with a database, you might want to check out a book specifically on database modeling as it might provide some good insights on how to create a good design. You can, however, proceed on in this guide, but you know the basic concepts of relational databases as these will not be presented here.

Content Management System database

In order to go through all the areas of database access in Morfik and the Morfik Framework this article (and several other articles) uses a sample database schema for a totally database-website. This is the Content Management System (CMS) database, and it can be created with the information provided in this and other articles about database development. In fact, it is recommended that you now create a project called CMS, create tables and attempt to reproduce the application as described.

The Content Management System database is as simple, yet achieves its intended goal of driving a complete website. It stores information about the site itself, site sections, articles posted and users.

Tables

Tables are the structures in which you store information in a relational database. Morfik treats tables as first class citizens within a project and they can be individually viewed in the Project View.

How you store information in tables is a very important aspect of creating a web application. Morfik creates a default database for each project, providing the means for you to create tables with the Table Designer. Morfik also allows you to add tables from other databases to your project. They will be perceived as any other table in the project, but will not be editable. Read More...

When creating a database you must first know which data you want to store. For this demonstration we will presume that we are going to be building a Content Management System (CMS), so we will define what would be the minimum required tables for a basic implementation of such a system.

In this case, we will need to store and use information about the website as a whole such as its name, sections, articles posted, copyright and, of course, its users.

We should start with a table to store information about the website itself. What kind of information would that be? A name for the site, a description, copyright notice and general information about the site’s owner, be that a company or a person. Those would be, generally, the main things that we need to store about the website itself.

Creating a table

We will be creating a new table with the help of the built-in table creation wizard. In order to create the first table it is necessary to select the Project Ribbon and click on Document Wizards|Table. In Figure 1 you can see this option in the Ribbon highlighted in red.


2-1.png
Figure 1: Creating a table by using the built-in wizard (highlighted on the Project Tab of the Ribbon).


You will be asked to provide a name for the table. In this case we are going to call the table WebsiteInfo as it will be holding all information which is related to the site itself. You can see in Figure 2 the first page of this wizard.


2-2.png
Figure 2: First step in the Create Table wizard: entering a table name.


Once you have typed in a name for the table, you can move on to the next step where you will be asked which fields to include in the table. Figure 3 displays the second step in this wizard.


2-3.png
Figure 3: Second step in the Create Table wizard: adding fields to the table.


You can add fields/columns to the table by clicking on the Add button. This brings up a small dialog that lets you type in the name and data type details necessary. In Figure 4 you can see the dialog for adding more fields to the table.


2-4.png
Figure 4: The two views of the dialog which is used for adding fields to the table
in the Create Table wizard.


As can be seen in Figure 4, you can choose to just provide a field name and select a general type, or you can specify more details such as the size, default value and its description.

In this case a description was added to the field, specifying that this field will store the name of the website we are creating.

We should proceed to create this table with the fields matching the description in Table 1.

The default size for a text field is 64 so you just need to specify a size for the fields with lengths different to 64.

Table 1 – Field definitions for table WebsiteInfo
Field Type Size
Title Text 64
SubTitle Text 128
OwnerName Text 64
OwnerDescription Text 250
CopyrightMessage Text 128
CatchPhrase Text 128


Once all fields in Table 1 have been added to the wizard, you can proceed to the next step


2-5.png
Figure 5: Step two of the Create Table wizard with
the fields already added.

The third step of the Create Table wizard offers the developer an opportunity to define a primary key.


2-6.png
Figure 6: Third step of the Create Table wizard offers the opportunity to select a primary key for this table.


Selecting the Primary Key

Every record in a table must be identified by a field that holds a unique value. It is through this value that we will be able to retrieve the record in the future. This particular field is known as the primary key of the table. Relational database theory requires that all tables have a field which serves the purpose of being a primary key to guarantee that any on record can be uniquely identified and retrieved.Read more...

A table might contain more than one field that has unique values for each row, for example it might have a numeric id and a telephone. In this case both fields are what we call candidate keys and you must choose one of them to be the primary key.

One good way to evaluate candidate keys is to think about the possibility that they might change their value over time. For example, it is possible for a person to change phone numbers; in fact it is likely to happen over long periods of time. A numeric id, however, is under your system's control and won’t change unless the system needs it to. The numeric id is clearly the better choice as a primary key.

Defining a primary key is a very important aspect of table structure design. In many databases, this is the only way to uniquely identify the records in a table. Morfik does in fact definee a hidden field which serves this internal task, but in many cases it is still useful to define a primary key and not rely on the control field. The most common way to define a primary key is to use the AutoNumber data type.

You might notice that the table we are creating does not contain any fields that match the description of what would be a good candidate for a primary key. We can, however, go back to the previous step in the wizard and include such a field. If you would like to fully reproduce the example application, please return to the previous step in the wizard and add a field called Id defined in Table 2.

Table 2 – Id field definition for table WebsiteInfo
Field Type Size
Id AutoNumber (Integer) -


Once you have added the Id field you can return to the third step and select this field as the table’s primary key. The wizard, with the new field selected as primary key, can be seen in Figure 7.


2-7.png
Figure 7: The Id field is selected as primary key in the WebsiteInfo table in the third step of the Create Table wizard.


This completes the third step and final step of the wizard. The next page indicates that all information has been acquired and asks if the user wishes to open the table in Data View. This can be seen in Figure 8. Data View allows you to directly input data into the table—a very straightforward process that you should now try.


2-8.png
Figure 8: The Create Table wizard is complete. The table will be created as soon as Finish is pressed.

CMS Tables

In order to implement a content management system, however basic it is, we need to make use of several tables. If you wish to attempt to recreate this application you should create additional tables as defined below:

Table 3 – Definition for table: 'Section'
Field Type Size
Id AutoNumber (Integer) -
Title Text 64
SubTitle Text 128


The Id field in the 'Section' table should be the primary key.

Note that the structure of the 'Section' table is not very different from the 'WebSiteInfo' table. This is because a section of a website is essentially a small website in itself. The website, however, will have multiple 'Sections' under which we will be able to group the articles published.

Table 4 – Definition for table: 'UserCatalog'
Field Type Size
Id AutoNumber (Integer) -
FullName Text 64
Email Text 64
Password Text 20


Again, the Id field should be the primary key. This table will hold the list of users that can log in to our website and access restricted areas.

Table 5 – Definition for table: 'Article'
Field Type Size
Id AutoNumber (Integer) -
Title Text 64
Summary Text 250
Body Memo -
DateCreated DateTime -
DatePublished DateTime -
IsPublished Yes/No -
SectionId Number (Integer) -
CreatedBy Number (Integer) -


The Id field should again be the primary key. The 'Article' table will store the individual articles that compose our website. 'Article', in this case, is a generic name for the specific group of data which might hold a news item, a blog post or anything which can fit the pattern of having a title, a summary and a full body. This pattern is repeated in websites across the Internet. The additional fields in the 'Article' table are more for internal control of how the website works than for external consumption of the information they hold. The IsPublished field, for example, will allow for an article to be added to the database without it becoming immediately visible on the website. In our sample project the IsPublished field is used to filter content in the 'GetArticlesAndSections' and 'GetArticlesFromSingleSection' queries outlined below.

Note: Another table called UserCatalog will be specified in another article of this reference.

This table will be used to hold user name and password information for sign-in operations and a Web Method will be defined to work with it. For more information refer to Writing code for data manipulation.

Table Designer

The Table Designer is a specialized part of the Morfik development environment that allows you to define the structure of tables in your project's default database. Within the Table Designer you can add the fields you want and choose the appropriate data type for each one of them.

Morfik's Table Designer allows you to create one or more indexes for your Table and choose their composition to enhance the performance of data retrieval operations. The Table Designer enables you to create tables or visualize their structure from within Morfik. The web application projects enable you to create tables in the project's associated database (all web application projects have a corresponding database created by the built-in database management system). Read more...

Once tables are created, their structure can be modified through the Table Designer. The Table Designer allows each and every characteristic of a table to be set or altered in a manner that is simple and easy.

Through the Designer you can: add and remove fields, set and remove the primary key for the table, create and delete indexes, alter data types and so on. Figure 9 shows a view of the WebsiteInfo table in Table Designer


2-9.png
Figure 9: The WebsiteInfo table in the Table Designer in the Morfik development environment.


Using the Table Designer you can even reorder the fields in your table through a simple drag and drop operation. In Figure 9 the 'Id' field of the 'WebsiteInfo' table is last in the list. In Figure 10 this field is now the first. It will stay that way after you save the changes.


2-10.png
Figure 10: WebsiteInfo table in the Table Designer showing the Id field as the first in the list.


Through the Table Designer you can also enter data directly into a table. This is done through the Data View of the Table Designer. To use this feature you should select the 'Data' tab of the designer. This feature is especially useful to enter test data for your application. We will need some test data for our application so we can enter a record in the WebsiteInfo table. The WebsiteInfo table will always hold only one record as it contains general information about the website we are creating. In Figure 11 you can see a screenshot of the Morfik Workspace showing test data being entered for the WebsiteInfo table.


2-11.png
Figure 11: Data View of the Table Designer in the Morfik development environment.


Creating Relationships

Once we have created our tables, the next step in creating a database driven application/website is to create the relationships between those tables. Relationships, as the name suggests, indicate how data in one table relates to data in another table.

The Relationship Designer

The Relationship Designer allows you to visually establish relationships between the tables within your project's default database. The Relationship Designer will automatically handle modifications to your underlying database that might be required to support a relationship you create within it.

The Relationship Designer can be accessed from the View tab of the Ribbon. Read More...

Creating queries

Morfik brings to the creation of web-based applications an interesting approach to working with databases. Morfik allows developers to save queries which will be used through out an application so that they can be easily used as the data source for the construction of parts of the application interface. This is a feature also found in Microsoft Access and users who are familiar with that tool should feel right at home in working with them in Morfik.

Queries can be created in three different ways: entering the SQL code, using a Wizard or using the Query Designer.

Query Designer

The Query Designer is a specialized part of the Morfik development environment which allows you to define queries for usage in your application, either visually or by directly writing SQL language statements.

In its Design View, the Query Designer provides as easy interface for the creation of queries. In Code View, the Query Designer allows you to directly enter any SQL language statement which is valid for the target database. Read more...

Using the Query Designer in the Morfik development environment is very straightforward. When you select the 'New Query' option under 'New Item' in the Power Menu a small dialog pops up asking for the name of the query to be created, as shown in Figure 12. You are also asked if you will create a visual, non-visual or pass-through query.


2-12.png
Figure 12: Specifying a name for a query that is being created.


Once that is done the Morfik development environment will be in the Query Designer. In the Query Designer the first thing to do is to add tables to the query. In this example we will create a query to recover a single specific record with information from two tables. This query, called 'GetOneArticle', will recover all the information necessary for properly displaying a complete view of an Article. You can add tables to the query by clicking on the Show Tables button in the toolbar. This will bring up a dialog as shown in Figure 13.


2-13.png
Figure 13: Adding a table to the Query using the Data Sources dialog.


When adding both the 'Article' and 'UserCatalog' tables to the query the relationship between the tables is brought in automatically and displayed as shown in Figure 14.

defining-data-schema-query-relationship.png
Figure 14: Queries fields selected and the relationship between the tables added automatically.

In Figure 14 several fields have been selected for recovery:

From table Article:

Id, Title, Body, DateCreated, DatePublished,

From table UserCatalog:

FullName

On field Id of the Article table a parameter has been included in the query so that the developer can specify exactly what article he/she wants recover. This is called a parameterized query and can be very useful, especially for usage with forms. This will be covered in more detail in the next chapter. As the developer makes his/her choices in the Query Designer, Morfik is creating the SQL command which will be stored.

SQL code for the query above.

SELECT ALL
    "Article"."Id", 
    "Article"."Title", 
    "Article"."Body", 
    "Article"."DateCreated", 
    "Article"."DatePublished", 
    "UserCatalog"."FullName", 
    "UserCatalog"."Id"
FROM
    ("UserCatalog" INNER JOIN "Article" ON ("UserCatalog"."Id"="Article"."CreatedBy"))

Figure 15 shows the Morfik Project View with our first query selected.

In the Visual Database Programming topic, we will see more details about how to use these stored queries and how they integrate with the visual programming side of Morfik in a manner which is, at the same time, simple and powerful.


2-16.png
Figure 15: The GetOneArticle selected in the Project View. The Project view is being filtered to only show queries.


Wrapping it up

Morfik includes a fully featured relational database server which can be generally worked on through the Morfik Workspace. There are several different ways to create tables and queries, all of which are quite simple and powerful. Morfik also allows for the possibility of creating "pass-through" SQL commands which will essentially allow you to make use of just about any feature offered by the underlying database.

Related Topics

Back to top