Let’s create a transaction for countries (called Country):
Create a country identifier attribute. As you can see, GeneXus assigned it the Id domain.
Create an attribute to store the name of the country, CountryName, which takes the Name domain.
Note that just as it did with Customer, GeneXus has automatically created the form to add, edit and delete countries.
Let’s go back to the Attraction transaction, so as to assign a country to each attraction. Note that typing the letter C displays the list of attributes existing in the Knowledge Base that begin with this letter.
Select CountryId, and its entire definition is displayed.
Also, include the CountryName attribute in this transaction. It's useful to see the country´s name when this transaction is executed and a country identifier is selected
Let’s focus on these two attributes included in more than one transaction, in order to find out what role they play in Attraction.
Remember that CountryId is the identifier or key in the Country transaction:
To be more precise,CountryId is the primary key of the Country transaction and when a primary key is included in another transaction, it has the role of a foreign key.
Including an attribute that is a transaction's primary key in another transaction relates them both. This means that when executing the Attraction transaction, for this attribute you will have to enter a value that has been previously recorded through the Country transaction.
Let’s see it at runtime.
Remember to open the tunnel to connect the schema to the HANA database.
Press the Build All option.
GeneXus analyzes the impact caused by the new definitions made on the Knowledge Base
and informs you that a new table called Country will be created in the database with the CountryId and CountryName fields.
In addition, a new table called Attraction will also be created with the fields AttractionId, AttractionName and CountryId.
Note that in the Attraction physical table that GeneXus will create, the CountryName attribute is not included, even though you had included it in the Attraction transaction structure. This happens because the TRANSACTION concept is not the same as the physical TABLE concept. Remember that TRANSACTION is the GX object created in the Knowledge Base to represent an object or actor of reality. By examining it, GeneXus creates a PHYSICAL TABLE in the database, to store the data that will be entered when executing the transaction.
Not all the attributes included in a transaction structure will later be stored in the physical table created based on this transaction.
Storing the country´s name in several physical tables would mean storing duplicate data.
Instead, the country name can be retrieved from a single location where it is stored, in this case, from the country table.
Go back to the development environment and click on Reorganize. The term Reorganize means to reorganize the database. It refers to the task of making changes to it.
GeneXus creates the programs to change the database and executes them, making any necessary changes. Next, it generates all the programs corresponding to the application itself. For example, for each new transaction that has been defined, programs in the selected programming language to enter, change and delete countries and tourist attractions were generated.
Go to View \ Other Tool Windows \ Launchpad
Now, there are links to work not only with clients but also with attractions and countries.
Let's add data about some countries. Click on Country and this will open the browser.
Since the CountryId attribute was previously defined as belonging to the Id domain and this domain has the Autonumber property set to True, there's no need to enter a value for the identifier because it will be numbered automatically. Add Brazil, France and China.
And execute the Attraction transaction.
Add the tourist attraction “Louvre Museum”. You don’t have to enter a value for the identifier, so just type the name “Louvre Museum”. Indicate that the Louvre Museum is in France.
If you remember the identifier number of France, enter it.
Note that the country´s name can´t be changed from here, because it is displayed only for reading purposes.
Remember that a selection arrow was automatically displayed next to CountryId, providing a list of available countries
The arrow was displayed next to this attribute in particular because, as previously stated, CountryId is a foreign key here.
So, here the user will have to enter a value that has been previously registered as primary key value through the Country transaction... Therefore, GeneXus collaborates by generating and offering a list of countries available.
Now, you will see at runtime how the Country and Attraction transactions check that the values entered for the CountryId attribute are consistent.
Enter a new attraction, such as the “Pyramids of Egypt”. In the country field, enter the value "4", but an error message is displayed because country number 4 doesn’t exist!
Check the registered countries and you'll see that only countries 1, 2 and 3 have been entered, but not 4.
Likewise, if you want to change an attraction that has already been entered and try to replace its country with another one that doesn't exist —4 again— you'll get the same error message:
That is to say, when you enter or change data through transactions, the associated data is automatically checked for consistency. Also, when trying to delete data through transactions, the necessary controls are made to maintain the consistency of the stored data.
Now, for instance, when trying to delete the country France:
A message will be displayed, informing that the deletion cannot be performed because related data exists in Attraction (remember the Louvre Museum, which belongs to France).
Something very important to take into consideration is that attributes must be named using exactly the same name when they are related to the same concept.
For example, suppose that you type "CountryIdentifier" instead of "CountryId" in the Attraction transaction. They will be different attributes for GeneXus.
Therefore, GeneXus won't check whether the value entered in the CountryIdentifier attribute of the Attraction transaction exists in the table Country.
Hence, it won't offer the country selection list in the Attraction transaction.
Also, it won't be possible to retrieve the name of the corresponding country. Because CountryName is referenced in the Attraction transaction due to the fact that CountryId plays the role of foreign key, retrieving its corresponding CountryName. However, CountryIdentifier is not a foreign key, as it isn’t the primary key of any transaction, so it isn’t possible to retrieve data associated with this attribute.
Let’s represent more features of the travel agency's reality. Another requirement is that every attraction has an associated category to indicate if it is a monument, museum, park, and so on.
Here, the same situation that we saw with countries arises. Create a "Categories" transaction and assign the attractions’ categories.
Let’s do it now. Create the Category transaction with CategoryId and CategoryName.
Now, add the CategoryId and the CategoryName attributes to the Attraction transaction.
Before trying this at runtime, allow the category not to be set, as you don't know its value at the time of entering the attraction.
This is done by changing the value of the Nullable property of the CategoryId attribute. Set it to Yes:
This only makes sense for foreign keys that reference values from another table.
Let’s implement another request of the travel agency: For each attraction, they want to enter its photo. So, in the Attraction transaction, enter an attribute called AttractionPhoto.
It will be of Image type because this type makes it possible to store images.
Now, press F5 to apply the changes to the database and programs and run the application.
Note that a new table will be created in the database to store the categories.
Click on Attraction:
The Attraction table has to be converted, which means that the CategoryId and AttractionPhoto attributes will be added.
This element is added to store the file and to give the option to only reference a URL to it.
Press the REORGANIZE button.
Run the Category transaction from the Launchpad
Add the categories Museum and Monument.
Execute the Attraction transaction, note that it allows you to enter a category and a photo.
Search for the Louvre Museum, assign the Museum category and a photo to it.
A special feature of SAP HANA makes it possible to use table row storage instead of column storage.
To do so, pay attention to when View/Tables is opened:
Here, GeneXus shows all the tables of the corresponding database.
By clicking on one of the tables, you will see that among its properties there's one that is related to the type of storage in a SAP Hana database.
If you're trying to create a table and the percentage of transactional operations (that is to say, INSERT, UPDATE and DELETE operations) is very high, using row storage is recommended, because it is the default value. Also, it is how traditional relational DBMSs work.
For SAP Hana, there is an option to use column storage, which is useful when the table being designed has a very high percentage of reading operations (SELECT).
If this is the case of ATTRACTION, change that value.