# Database Preparation

In this step, you will prepare the **database structure and sample master data** required for the Workforce App.\
The application uses a PostgreSQL database schema named **`ERP_ONEWEB`** to store workforce benefit information, which will later be displayed on the mobile UI via Microflows.

This database setup acts as the **backend data source** for the Workforce App.

***

### Objective

In this section, you will learn how to:

* Create a master table in the `ERP_ONEWEB` schema
* Create a sequence for record management
* Insert sample data for testing and demonstration
* Prepare backend data for use in Microflow and Mobile UI

***

### Database Requirement

* **Database**: PostgreSQL
* **Schema**: `ERP_ONEWEB`
* **Table**: `ONEWEB_BENEFIT`
* **Data Usage**: Workforce travel benefit records

***

### Step 1: Create Master Table

Execute the following SQL to create the **`ONEWEB_BENEFIT`** table.\
This table stores employee travel benefit details such as project name, benefit type, amount, and status.

```
CREATE TABLE erp_oneweb.oneweb_benefit (
                id CHARACTER VARYING(20) NOT NULL,
                travel_date TIMESTAMP(6) WITHOUT TIME ZONE,
                project_name CHARACTER VARYING(200),
                description CHARACTER VARYING(500),
                amount NUMERIC(10,2),
                status CHARACTER VARYING(20),
                benefit_type CHARACTER VARYING(20),
                create_by CHARACTER VARYING(100),
                create_date TIMESTAMP(6) WITHOUT TIME ZONE,
                update_by CHARACTER VARYING(100),
                update_date TIMESTAMP(6) WITHOUT TIME ZONE,
                username CHARACTER VARYING(100),

                CONSTRAINT oneweb_travel_alw_pkey PRIMARY KEY (id)
);
```

This table will later be accessed by Microflows to retrieve data for the mobile application.

***

### Step 2: Create Sequence

Create a database sequence to support incremental ID generation.

```
CREATE SEQUENCE erp_oneweb.oneweb_benefit_seq
START WITH 10
INCREMENT BY 10
NO MAXVALUE
NO MINVALUE
CACHE 20
NO CYCLE;
```

> Note\
> In this tutorial, sample records use fixed IDs for simplicity, but the sequence can be used in real‑world scenarios for automated ID generation.

***

### Step 3: Insert Sample Master Data

Insert sample records into the `ONEWEB_BENEFIT` table using the SQL below.\
These records represent approved travel benefits for a workforce user and will be displayed on the mobile UI later.

```
INSERT INTO oneweb_benefit
(id, travel_date, project_name, description, amount, status, benefit_type, create_by, create_date, update_by, update_date, username)
VALUES
('1', '2019-07-06 15:41:38', 'AVA PROJECT', 'Taxi', 1000, 'Approved', 'Travel', null, null, null, null, 'Alisha');

INSERT INTO oneweb_benefit
(id, travel_date, project_name, description, amount, status, benefit_type, create_by, create_date, update_by, update_date, username)
VALUES
('2', '2019-07-06 15:41:38', 'AVA PROJECT', 'BTS', 500, 'Approved', 'Travel', null, null, null, null, 'Alisha');

INSERT INTO oneweb_benefit
(id, travel_date, project_name, description, amount, status, benefit_type, create_by, create_date, update_by, update_date, username)
VALUES
('4', '2019-07-06 15:42:54', 'AVA PROJECT', 'MRT', 200, 'Approved', 'Travel', null, null, null, null, 'Alisha');

INSERT INTO oneweb_benefit
(id, travel_date, project_name, description, amount, status, benefit_type, create_by, create_date, update_by, update_date, username)
VALUES
('5', '2019-07-06 15:42:55', 'AVA PROJECT', 'BRT', 400, 'Approved', 'Travel', null, null, null, null, 'Alisha');

INSERT INTO oneweb_benefit
(id, travel_date, project_name, description, amount, status, benefit_type, create_by, create_date, update_by, update_date, username)
VALUES
('6', '2019-07-06 15:42:57', 'AVA PROJECT', 'Taxi', 250, 'Approved', 'Travel', null, null, null, null, 'Alisha');
```

***

### Verification

After executing the SQL scripts:

* The table `erp_oneweb.oneweb_benefit` should exist
* Sample data should be present in the table
* The data will be used in the next steps to:
  * Create Microflows for data retrieval
  * Display records on the Workforce mobile UI

You can verify the data using:

```
SELECT * FROM erp_oneweb.oneweb_benefit;
```

***

### Summary

In this section, you have:

* Created the master table for workforce benefit data
* Prepared a sequence for record handling
* Inserted sample data for development and testing

This database preparation completes the **backend foundation** for the Workforce App.\
In the next steps, you will build **Microflows** to retrieve this data and connect it to the **mobile user interface**.


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://docs.onewebstack.com/oneweb-platform-th/tutorials-examples/workforce-app-mobile/database-preparation.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.