Morfik Database Guide - Database Modeling in Morfik
From Morfikwiki.com
This topic will provide you will a general overview of how to create and work with your database model inside the Morfik AppsBuilder Workspace. 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, MySQL, etc.
Our focus will be on providing a quick reference for how to use the built in tools and features of the Morfik AppsBuilder Workspace to construct a relational model for a complete application which we will build mostly through out the topics of this guide.
Morfik comes with a built in database which is a full featured database server. This is an open source database called Firebird which can be freely deployed with no licensing costs. Morfik simplifies the usage 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.
Contents |
[edit] Database Model
Morfik has built into the AppsBuilder Workspace the concept of your database model, represented by the Relationship View, where you can add your tables and visually 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 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. It is not, however, a required step to proceed on in this guide. It is required however that you know the basic concepts of relational databases as these will not be presented here. |
[edit] Content Management System Database
In order to go through all the areas of database access in Morfik AppsBuilder and the Morfik Framework this guide uses a sample database model for a totally database driven website. This is the Content Management System (CMS) database. This is the database for a project that will be created in this guide, starting in this chapter. In fact, it is recommended that you go right ahead and create a project called MorfikCMS if you intend to follow through the steps described here.
The content management database is as simple as can be made and yet achieve its intended goal of driving a complete website. It stores information about the site itself, site sections, articles posted and users.
[edit] Tables
The first thing in creating a database is to know which data you want to store. For the purpose of illustrating this guide, we are going to be building a Content Management System (CMS) so we will focus on creating the required tables.
In this case, we have stated that we are going to be storing and using information about the website, its sections, articles posted to it, and its users.
First lets 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.
[edit] 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 Tab in the Ribbon on the top portion of the Workspace. This this option in the Ribbon is highlighted in Figure 1.
Figure 1 – The option to create a table by using the built-in wizard is highlighted on the Project tab of the Ribbon.
Choosing the New Table option from the New Item submenu of the Morfik menu brings up the wizard where you will be asked to start by providing a name for the table which is about to be created. In this case we are going to call the table WebsiteInfo as it will be holding all information which related to the site itself. Figure 2 shows the first page of this wizard.
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 shows the second step in this wizard.
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. You can see this dialog in figure 4.
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 actual size, in case of a text field, 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 |
| HomeSection | Number (Integer) | - |
Once all fields have been added to the wizard, matching the definitions in Table 1, you can proceed to the next step
The third step of the Create Table wizard offers the developer an opportunity to define a primary key.
Figure 5 – Step two of the Create Table wizard with
the fields already added
Figure 6 – Third step of the Create Table wizard offers the opportunity to select a primary key for this table.
[edit] Selecting the Primary Key
Good relational database design practices dictate that every table should have a primary key. A primary key is a column that uniquely identifies each row, allowing for no duplicate values. A table might contain more than one column that has unique values for each row, for example it might have a numeric id and a telephone. In this case both columns 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, while a numeric id is under your system’s control and won’t change unless the system needs it to. Comparing these two candidate keys in this light the numeric id is the better choice as a primary key.
Defining a primary key is a very important aspect of table structure design. In many databases, this task is absolutely necessary as it is the only way to uniquely identify the records in a table. Morfik, however, defines a hidden field which serves this internal task. In many cases, however, 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.
As you might notice this 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. Please do that and add a field called Id with the definition presented 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 back 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.
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 of the wizard and we can move on. This will actually conclude the wizard since 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 for direct input of data into the table. Though we will enter some data further in this guide, we will not dispend time on it now.
Figure 8 – The Create Table wizard is complete. The table will be created as soon as Finish is pressed.
[edit] CMS Tables
In order to proceed through this guide, if you wish to recreate the application that is being used as a sample here, you should create additional tables with structures as shown in the following tables.
| Table 3 – Definition for table Section | ||
| Field | Type | Size |
| Id | AutoNumber (Integer) | - |
| Title | Text | 64 |
| SubTitle | Text | 128 |
| IsPublic | Yes/No | - |
| ParentId | Number (Integer) | - |
In table Section, again, the Id field should be the primary key.
Note that the structure of the Section table is not very different from the WebSiteInfo table. That is because a section of a website is essentially a website in itself. Section, however, has a self referencing relationship where you can specify that a section is a subsection of a previously existing one. This is done through the ParentId Field.
| Table 4 – Definition for table UserCatalog | ||
| Field | Type | Size |
| Id | AutoNumber (Integer) | - |
| FullName | Text | 64 |
| Text | 64 | |
| Password | Text | 20 |
In table UserCatalog, again, the Id field should be the primary key. This table will hold the list of users that can log in to our website to have access to 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) | - |
In table Article, again, the Id field should be the primary key. The Article table will store the individual articles that compose our website. Article, in this case, is a generic name that I have given to this 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 can be found to repeat itself in websites all across the Internet, no matter what the site’s content is. 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 to the website’s visitors.
| Note | |
|
| Another table, called UserCatalog, will be specified in another topic 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 the Working with Database Connections topic. |
[edit] Table Designer
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, etc. Figure 9 shows a view of the WebsiteInfo table in Table Designer
Figure 9 – The WebsiteInfo table in the Table Designer in the Morfik Workspace.
Using the Table Designer you can even reorder physically the fields in your table through a simple drag and drop operation. This is the field we added to the WebsiteInfo table, after having added all the others. In Figure 9 the Id fields of the WebsiteInfo table is the last one in the list. In Figure 10 this field is now the first one in the list and will stay that way, after you save the changes, each time the table is opened in the designer.
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.
Figure 11 – Data View of the Table Designer in the Morfik Workspace.
[edit] 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.
Figure 12 – List of tables to be added to the Relationships View.
From the View Tab of the Ribbon you can choose the Relationships view which leads to a blank slate, if relationships have not been created in the application. Tables can be added to the Relationships View by right clicking the background and selecting "Show Table". This brings up a dialog with the list of tables in the current project as can be seen in Figure 12.
Figure 13 – All tables from the CMS project just added to the Relationships View.
Once all the project’s tables have been added to the Relationships View the actual relationships can be created. In order to create a relationship between two tables the user should drag the primary key of a table over the corresponding (foreign key) field in the related table.
In the case at hand there are two relationships to be defined. The first is between tables Section and Article while the second is between Article and UserCatalog. This is because an article will be published under a section of the website and created by one of the site’s users.
Once the drag and drop operation is completed, a dialog box will popup asking for the specific details of the relationship that is being created. This dialog is shown in Figure 14.
Figure 14 – Edit Relationships dialog showing a relationship between tables UserCatalog and Article.
At this moment the user can specify if referential integrity will be automatically enforced by the underlying Firebird database server. In Figure 15 these options are highlighted.
Figure 15 – Edit Relationships dialog of the Morfik Workspace with Referential Integrity options highlighted.
As defined in Figure 15 the relationship between tables UserCatalog and Article will cascade any changes. What this means is that if a User has his Id changed in the UserCatalog table, all corresponding articles will get updated to reflect the new Id. This avoids the possibility of ending up with inconsistent data in the database. The cascade delete option means that if any user is deleted from the UserCatalog table, all articles created by that user will also get deleted.
| Note | |
|
| In this case, in the interest of simplicity of design the Cascade Delete option is being turned on. Generally speaking this is not a good idea for a production system. A strong argument can be made that if someone accidentally deletes a record you do not wish to run the risk of having tons of other records deleted, in other tables. |
In Figure 16 you can see the complete relationship map for the tables in the MorfikCMS database, with the two relationships created.
Figure 16 – Relationships diagram for the tables in the MorfikCMS project.
Note that in Figure 16 you can see a relationship between tables Section and WebsiteInfo. This is due to the fact that you select one of the existing sections to be your home section, i.e. the section that will be shown when the end user first types in the application/site’s URL.
[edit] Creating Queries
Morfik AppsBuilder 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 in the AppsBuilder: entering the SQL code, using a Wizard or using the Query Designer.
[edit] Query Designer
Using the Query Designer in Morfik AppsBuilder’s Workspace is very straight forward. When you New Query option under New Item in the Morfik Menu a small dialog pops up asking for the name of the query to be created as shown in Figure 17. You are also asked if you will create a a Visual, non-visual or passthrough query and what Morfik language should be used for the server side components that accompany each data source, such as a table or a query.
Figure 17 – Specifying a name for a query that is being created.
Once that is done the Workspace goes into 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 from two tables. This query, called GetOneArticle will recover all the information for displaying a complete view of an Article to the website’s visitors. You can add tables to the query by click on the Show Tables button in the toolbar which will bring up a dialog as shown in Figure 18.
Figure 18 – 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 19.
Figure 19 – Queries fields selected and the relationship between the tables added automatically.
In the case shown in Figure 19 the several fields have been selected for recovery.
From table Article:
Id, Title, Body, DateCreated, DatePublished, DateCreated
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 AppsBuilder is creating the SQL command which will be stored.
Figure 20 – SQL command generated by the Query Designer.
Figure 21 shows the AppsBuilder 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 AppsBuilder in a manner which is, at the same time, simple and powerful.
Figure 21 – The GetOneArticle selected in the Project View. The Project view is being filtered as to only show queries.
[edit] Wrapping it up
Morfik AppsBuilder 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 AppsBuilder 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.
