Skip to Content

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:

Data Model.png

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.

Create Database.png

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

DB overview.png

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.

Create Table.png

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.

Table Columns.png

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:

Tables finished.png

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.

Edit structure.png

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

create index.png

The index is then listed in the index overview.

index overview.png

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.

relation view.png

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.

relations 1.pngrelations 2.png

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

To report this post you need to login first.

Be the first to leave a comment

You must be Logged on to comment or reply to a post.

Leave a Reply