The Logical Data Model

In the logical data model, we’ve defined five entities that form the foundation of our app. Let’s break them down:

1. Client: This entity stores information about our clients, including their company name, contact person, contact person’s email, and agreed hourly rate. Each client can have multiple entries in the work diary.

2. Work Diary Entry: Here, we record the core data for each work entry: entry ID, date, start and end timestamps, lunch break duration, and whether the entry has been invoiced yet. It’s the bread and butter of time tracking.

3. Invoice: Invoices are essential for getting paid. This entity includes data like invoice ID, issue date, due date, total amount, currency, and status. Invoices can be marked as ‘paid’ or ‘overdue,’ keeping your finances in check.

4. Invoice Item: Every invoice can consist of multiple items. We track them with their own IDs and partial amounts for precise billing.

5. Currency Exchange Rate: This entity isn’t directly connected to the others, but it’s vital for currency conversion. It records currency code, the current exchange rate, and the timestamp of the record. We will use this entity to store information from an outside data source REST API.

Crafting the Relational Model

To bring our logical data model to life, we used Oracle SQL Data Modeler’s ‘Engineering to relational’ function. This resulted in a relational data model, a crucial step in database development.

Generating the Database Schema

With our relational model in hand, we generated a script for creating tables, sequences, and triggers using Oracle SQL Data Modeler. After importing and executing the script in Oracle APEX via ‘SQL Workshop,’ we ensured the data integrity by manually adding specific constraints.

Finally, we inserted test data into our tables.

The script, specific constrains and test data is available for download via my GitHub.

The data model is the backbone of “REST Work Diary,” and it’s the foundation upon which our app stands tall. In the next post, we’ll explore how we bring this model to life using Oracle APEX. Whether you’re a tech enthusiast or a budding developer, stay with us on this exciting journey of web app creation!