Database Design Problems | Design a Multiple Job Payments Tracking System

March 10th, 2024

Database Design Problem Introduction

In continuing to get experience with designing database solutions for different problems, I’ve stumbled upon a now-defunct website called Database Answers. This website reviews many example problems related to database design and gives solutions to said problems. Today, we’ll discuss a solution to storing payment information related to multiple jobs at once. A synopsis of the problem is shown below:

The Area being Modelled is:
Subject: Payments Database Modeling
Question:
Goal: A database for tracking personal salaries from different jobs.

Explanation: I work two types of jobs.
For one job the work schedule is fixed, and the corresponding payments are on a regular monthly basis, periodically.

For the other type of jobs the schedule is quite irregular, and I get paid by hours.

Here is my dilemma.

If I use one master table to record every payment entry, I will have to decide either using hour or month as pay period intervals.

If I use hour as pay period interval, it will accommodate my ad hoc jobs nicely but not for my regular job.

I will have to divide my monthly wage from regular job by the total working hours in that month.

Not only would it make the table clutter, it would also demand a lot of repeating input for periodic information
(e.g. 9am to 10am; 10am to 11am....for everyday!) with exactly the same values.

On the other hand, if I make the payment interval monthly instead of hourly, it will definitely accommodate my
regular job better, but will lose a lot of information that I should keep track of for those ad hoc jobs.

I could make two different tables but I suspect that would make JOIN very complicated without resorting triggers.

Is there any templates or reference that tackles similar problem ?

Any suggestion would be highly appreciated.

Thanks.

Database Design Solution

Payments Table

RegularJobPayments Table

AdHocJobPayments Table

Database Design Solution Summary

The database schema is designed for managing payments with a focus on regular job payments and ad-hoc job payments. Here’s a summary of the key components:

Payments Table:

• Columns:
  • id (Primary Key): A unique identifier for each payment.
  • value: A floating-point number representing the payment amount.
  • payment_start_date: Date and time when the payment period starts.
  • payment_end_date: Date and time when the payment period ends.

RegularJobPayments Table:

• Columns:
  • id (Primary Key): A unique identifier for each regular job payment.
  • payment_id (Foreign Key): Links to the id column in the Payments table, establishing a relationship.
  • monthly_rate: A floating-point number representing the monthly payment rate for regular jobs.
  • Other properties specific to regular job payments.

AdHocJobPayments Table:

• Columns:
  • id (Primary Key): A unique identifier for each ad-hoc job payment.
  • payment_id (Foreign Key): Links to the id column in the Payments table, establishing a relationship.
  • hourly_rate: A floating-point number representing the hourly payment rate for ad-hoc jobs.
  • Other properties specific to ad-hoc job payments.

Key Points:

• The Payments table serves as a central entity for general payment information.
• RegularJobPayments and AdHocJobPayments tables are linked to the Payments table through foreign key relationships.
• Regular job payments have a monthly rate, while ad-hoc job payments have an hourly rate.
• Each payment, whether regular or ad-hoc, is uniquely identified by its id.
• The schema allows for flexibility in adding additional properties specific to regular and ad-hoc job payments.