Kick-Off: Database Design

Overview

With a solid understanding of prompt engineering fundamentals and advanced techniques, it’s time to apply this knowledge to building our document expiration tracker application. We won’t have time to make a full-blown app, but we’ll focus on its core features.

This chapter marks the project kick-off, focusing on the crucial first step: designing and implementing the database. We will use ChatGPT, our AI assistant, to guide us through understanding the application requirements and generating the SQLite database schema.

Understanding the application requirements

Before diving into code, it’s essential to define what our application needs to do clearly. This clarity will be paramount when crafting effective prompts for the AI. Our document expiration tracker needs to:

·     Store document information: Each document (e.g., driver’s license, passport, contract) must have a name, type, expiration date, and optional notes.

·     Identify documents uniquely: Each document needs a unique identifier.

·     Track expiration dates: The primary function is to monitor and display documents based on their expiration dates.

·     Basic management: Users should be able to add, view, edit, and delete document records.

This simple set of requirements forms the basis for our database design. We will use SQLite for its simplicity and file-based nature, which make it ideal for a small, self-contained application like this.

Database design principles

Before we ask the AI to generate our schema, let’s briefly review some fundamental database design principles that guide our requirements:

·     Normalization: Aim to reduce data redundancy and improve data integrity. For our simple application, a single table is sufficient; for more complex systems, data would be split across multiple related tables.

·     Primary keys: Every table should have a primary key, which is a column (or set of columns) that uniquely identifies each record. This is crucial for efficient data retrieval and relationships.

·     Data types: Choosing appropriate data types for each column ensures data integrity and optimizes storage and performance. SQLite is flexible with data types, but it’s good practice to define them clearly.

·     Constraints: These ensure that critical fields are never left empty, maintaining data quality.

Database design principles

SQLite uses a more dynamic type system than other relational databases. It has five primary storage classes:

·     NULL: The value is a NULL value.

·     INTEGER: The value is a signed integer, stored in 1, 2, 3, 4, 5, 6, or 8 bytes depending on the magnitude of the value.

·     REAL: The value is a floating point value, stored as an 8-byte IEEE floating point number.

·     TEXT: The value is a text string, stored using the database encoding (UTF-8, UTF-16BE, or UTF-16LE).

·     BLOB: The value is a blob of data, stored exactly as it was input.

When you declare a type like VARCHAR(255) or DATETIME, SQLite uses a concept called type affinity to determine the storage class. For example, DATETIME columns typically have NUMERIC or TEXT affinity. For dates, storing them as TEXT in 'YYYY-MM-DD' format is often the most straightforward and compatible approach for sorting and comparison in SQLite without complex conversions.

Let’s use our AI assistant to help us design the SQLite database schema based on the requirements and these principles. We’ll employ the principles of clarity, specificity, and output format specification to get a precise statement.

Code Listing 4-a: DB Schema Prompt

After entering this prompt, we get the following result from ChatGPT.

Code Listing 4-b: DB Schema Prompt Result

The AI, acting as a SQLite database administrator, has provided a perfectly valid statement. Let’s break down its components:

·     id INTEGER PRIMARY KEY AUTOINCREMENT: This defines a unique identifier for each document. INTEGER PRIMARY KEY in SQLite automatically makes the column auto-incrementing, ensuring each new record gets a unique ID.

·     name TEXT NOT NULL: Stores the document’s name as text, and NOT NULL ensures that this field cannot be empty.

·     type TEXT NOT NULL: Stores the document’s type as text, also a required field.

·     expiration_date TEXT NOT NULL: Stores the expiration date as text. While SQLite supports various date and time functions, storing dates as 'YYYY-MM-DD' strings is a common and efficient approach for sorting and comparison without complex type conversions.

·     notes TEXT: An optional text field for any additional information.

This schema directly addresses all our initial requirements, demonstrating that a well-crafted prompt can yield precise, usable database definitions.

Creating the database and tables

Now that we have our CREATE TABLE statement, the next step is to actually create the SQLite database file and the documents table within it.

We can use PHP’s PHP Data Objects (PDO) extension to interact with SQLite.

Code Listing 4-c: Creating Database and Tables Prompt

After entering this prompt, we get the following result from ChatGPT.

Code Listing 4-d: Creating Database and Tables Prompt Result (create_documents_db.php)

This PHP script should handle the database creation. Let’s go over what it does:

·     $dbFile = __DIR__ . '/documents.sqlite';: defines the path for our SQLite database file. __DIR__ ensures it’s created in the same directory as the script.

·     new PDO('sqlite:' . $dbFile): This line attempts to connect to the SQLite database. If documents.sqlite doesn’t exist, PDO will create it.

·     setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION): This is a crucial setting that configures PDO to throw exceptions on errors, allowing us to catch them with a try-catch block.

·     CREATE TABLE IF NOT EXISTS documents: The IF NOT EXISTS clause is a good practice, preventing an error if the table already exists.

·     exec($createTableSQL): Executes the SQL command to create the table.

·     try-catch (PDOException $e): This block gracefully handles database errors, printing a user-friendly message and logging a detailed error message.

To run this script, save it as create_db.php in your web server’s document root (e.g., htdocs for XAMPP) and access it via your browser (e.g., http://localhost/create_db.php). Note that in my case, the Apache web server is running on port 80.

Figure 4-a: XAMPP Control Panel (Apache Web Server—Port 80)

Then, open the browser to run the PHP script.

Figure 4-b: Successful Execution of the PHP Script

After execution, you should find the documents.sqlite file in the same directory.

Figure 4-c: The documents.sqlite File Successfully Created

To view the content of the documents.sqlite file, install DB Browser for SQLite, and then run the app. Once the app is running, go to the File menu, click Open Database, and select the file.

Figure 4-d: The documents.sqlite File—DB Browser for SQLite

With the DB Browser for SQLite app, you’ll be able to see and manipulate the file structure and data.

Initial data population prompts

To make our application functional and testable, it’s helpful to populate the database with some initial data. We can again use our AI assistant, ChatGPT, to generate statements.

Code Listing 4-e: Generate Data Prompt

After executing this prompt, we get the following result from ChatGPT.

Code Listing 4-f: Generate Data Prompt Result

ChatGPT has generated a set of INSERT statements that populate our documents table with diverse data, including an expired document.

To execute these INSERT statements, we can create a new PHP script.

Code Listing 4-g: Prompt to Create PHP to Insert Data

Notice that we are not even mentioning the database or table name, because ChatGPT already knows the context from our previous prompts.

Code Listing 4-h: Create PHP to Insert Data Result (insert_documents_db.php)

We can save this file as insert_documents_db.php and then open the browser on the localhost to execute the script.

Figure 4-e: Successful Execution of the PHP Script (insert_documents_db.php)

If we open the documents.sqlite file, and then go to the Browse Data tab, we’ll see the inserted data.

Figure 4-f: The Inserted Data—DB Browser for SQLite

We are now ready to start with the core application logic.