Database Architecture

Database Architecture

The Phone Service System relies on a lightweight, embedded SQLite database (database.db). This allows the application to run smoothly without requiring any complex server configuration (like SQL Server).

🏗️ The Repository Pattern

All database interactions are abstracted using the Repository Pattern. The Repository.cs class acts as the single point of contact between the C# application and the SQLite database.

Benefits of this approach:

1. Separation of Concerns: UI forms do not contain any SQL logic.
2. Security: All queries use parameterized inputs to prevent SQL Injection.
3. Maintainability: If the database technology changes in the future, only the Repository.cs file needs to be updated.

📊 Database Schema

The database consists of three main tables:

1. Client Table

Stores information about the people subscribing to phone services.

• ClientId (INTEGER, Primary Key, Auto-increment)
• FirstName (TEXT, Not Null)
• LastName (TEXT, Not Null)
• PhoneNumber (TEXT, Not Null)

2. ExtraOption Table

Stores the available add-on services and their prices.

• ExtraOptionId (INTEGER, Primary Key, Auto-increment)
• Name (TEXT, Not Null)
• MonthlyCost (REAL, Not Null)

3. Subscription Table

A junction table that links Clients to Extra Options, along with the duration of the subscription.

• SubscriptionId (INTEGER, Primary Key, Auto-increment)
• ClientId (INTEGER, Foreign Key referencing Client)
• ExtraOptionId (INTEGER, Foreign Key referencing ExtraOption)
• StartDate (TEXT, Not Null)
• EndDate (TEXT, Nullable)

⚙️ Initialization

Upon the first launch of the application, the Repository.cs static constructor checks if database.db exists in the execution directory (bin/Debug or bin/Release). If it does not exist, it automatically creates the file and executes the CREATE TABLE scripts to set up the schema.