The power of OData - Part 3 - Creating a data Model

[marc_augustin2]

Content of this series:

The power of OData - Part 1 - Introduction

The power of OData - Part 2 - Setting up the Environment

      The power of OData - Part 3 - Creating a data model (this part)

The power of OData - Part 4 - Creating an OData service

The power of OData - Part 5 - Consuming the service with SAPUI5

A rather short blog post for today’s part. I want to create the needed tables and relations in my sample data model upon which we will create our OData service and UI5 application. To have some values, I will also fill the tables with some sample data. The following picture gives an overview about the demo model that I will use:

For this series, I will use phpmyadmin that was installed by the standard XAMPP installation. For bigger projects, MySQLStudio or similar editors might be better. To access phpmyadmin, go to http://localhost/phpmyadmin

To begin, we have to create a new database. Click on the database tab and enter a name. The collation can be left initial here; we don’t care about this for our tests.

After clicking the “create” button, our database is now visible among the demo databases that are created by the XAMPP installer:

Let’s click on it to begin creating the tables. We start with country and create all tables shown in the picture above before creating the foreign key relations.

I will go over the creation process with pictures for the first table and give the values to insert as a text table for each table.

Tick here the checkbox in column A_I for the countryID row to let the MySQL server handle the unique identifier.

Country table

Name	Type	Length	A_I
countryID	INT		X
country	VARCHAR	45

City Table

Name	Type	Length	A_I
cityId	INT		X
city	VARCHAR	45
countryID	INT

Address Table

Name	Type	Length	A_I
addressID	INT		X
street	VARCHAR	45
number	VARCHAR	45
state	VARCHAR	45
postalcode	INT
cityID	INT

Customer Table

Name	Type	Length	A_I
customerID	INT		X
firstName	VARCHAR	45
lastName	VARCHAR	45
email	VARCHAR	45
addressID	INT

After finishing with table creation, we should have the following overview:

To create the relations, we have to define some indices first. All foreign key fields in the tables have to be defined as index, and then we can link the two tables. I will again show this for one table relation, the other relations should then be straight forward.

To create a relation between the city and country table, we have to create a new index based on the countryID field in the city table. To edit the table again, we click on the Structure link.

Here, we simply click the Index link in the countryID row:

The index is then listed in the index overview.

Repeat this step with the following fields:

• cityID in address table
• addressID in customer table

Next we create the relation. We go again to the depending table, city in this example. Next, we click the “Relation View” link.

In this new dialog, we choose the table fields that are linked with the current one and define that there should be no action when a delete or an update is happening. Before saving our foreign key, we enter some name to identify it.

Now do the same for the address table and the customer table.

We are now ready to insert some data. As we have relations between the tables, we have to insert data in the following order:

1. country
2. city
3. address
4. customer

Entering data can be done via the build-in insert function, but I prefer to have SQL statements for entering mass data. I attach a text document containing all the INSERT statements to add some sample data. Additionally, I will provide a "build" script that will create all the tables relations and data as shown in this post to have a fast start on this.

Feel free to add more demo data to the address and customer tables to get a “better” result in our final application. So far, the data model is created and ready to use. In the next part, I will show how to create the corresponding OData service that will serve the data we just created. In the final part, I will then consume this service and link the retrieved data within an UI5 application.

Let's start and create some content: The power of OData - Part 4 - Creating an OData service