Quickstart building a web application (Web/Mobile)
Lesson 2: Defining the database
Now that the workspace is created the next step is to define the database. The database consists of multiple tables that are related to each other. In this lesson we will create tables that will be used throughout the complete course. These are the general steps to create a workspace, with the settings that are used in this Quickstart:
- Click on Create a table in the dashboard
- This table will be the Person table. It’s used to store persons with specific details.
- Choose the Embedded tab.
- Name the table Person.
- Click OK, now it creates the table.
- Create these columns:
- PersonID, Numeric, 8,0
- LastName, ASCII, 40,0
- FirstName, ASCII, 40,0
- Address, ASCII, 110,0
- Zip, ASCII, 9,0
- City, ASCII, 60,0
- Phone, ASCII, 25,0
- Comments, Text, 1024
- Define the indexes. These are used to determine how DataFlex is going to sort the data. The first step is always to create a unique index on the primary key of the table. In this case PersonId. Click Add Index, select PersonId in the Column field.
- Create another Index. Select LastName and FirstName. In this case you will be able to sort on LastName and FirstName. To make it unique select PersonId.
- To be able to sort on FirstName do the same as in step 9, but start with FirstName.
- Add the last index on City. Select City, LastName, FirstName and PersonId.
- All field have the proper Index, except FirstName. The reason to why it has Index 2 is because it's a segment of that Index. We will change it's Main Index to Index 3.
- Save the table.
- The Table Explorer is located on the left end of the studio. It shows an overview of the created tables. The tables CodeMast and CodeType were automatically generated. Later on in this project, more automatically generated tables will show up here.
- The next step is to create another table called "Media". It’s going to maintain records about media items. For example images or tracks. These records will be related to a person. Click on "Create New Table"
- Enter the name "Media" in the Table Name section.
- Enter the following data into the columns:
- MediaId, Numeric, 8,0
- PersonId, Numeric, 8,0
- Title, ASCII, 100,0
- Author, ASCII, 60,0
- Type, ASCII, 8,0
- PurchaseDate, Date, 6,0
- Price, Numeric, 4,2
- Add the Indexes. The first is on MediaId.
- The second Index will be Title, that way it can be sorted on "Title" and "MediaId". This will make it unique.
- The third index will contain PersonId, Title and MediaId. This will help when you want to see the media items of a specific person.This index will also help DataFlex find records faster.
- Now create a relation between the Person and Media table. Go to the Relationships tab.
- Click Add Relationship and select the Person table.
- Relate the PersonId of the Person table with the PersonId of the Media table.
- Create another table by clicking the Create new Table button. This table is a technical table, that will be used for auto numbering fields and several other configuration options. Call the table SysFile.
- The Sysfile will have two fields:
- PersonId, Numeric, 8,0
- MediaId, Numeric, 8,0
- Double clicking on the SysFile brings up the Properties Panel for this table.
- Set is system file to True.
- This finishes this lesson.
REFERENCE LIBRARY
Quickstart building a web application (Mobile touch)
