Help me design a database please

[CyberShot]

Hello all. I run the finances for an HOA and I have been looking all over the place for a tool that would help me keep track on HOA payment and I have just not been able to find anything that works well. I thought I would take a crack at making something myself. It has been many years since I have worked at creating a database and so I thought I would ask for help here. I need help creating the proper relationships with the correct primary and foreign keys so that my queries will pull up the correct information when I get to that part which I will also have to figure out how to do. Is this something you would be willing to help me figure out? What I would like to create is a better method of keeping track of the dues. Right now, I use a spreadsheet and an app that I paid 1.00 for. Both methods have flaws that i won't go into. What I want to make is something that will let me store the info by their unit number, log payments each month. Show how many months a unit may be behind in payment and the balance of that by month. Sometimes, I get a partial payment and I want that month to reflect that partial payment. If they make a payment for a previous month, I want to be able to apply it to that month instead of the current month and show the months that have not been paid.

 

 

Here is my initial thought. Can you help me improve this design?

 

Table Unit

unitNumber ( primary key )

firstName

lastName

owner

renter

emailAddress

 

Table Dues

ID

duesAmount

balance

paymentMethod

monthsMissed

paymentMonth

paymentDate

lateFee

 

[Barand]

Here are the bones of a model that could work for you

   +-------------+                               +---------------+
   |   unit      |                               |    renter     |
   +-------------+                               +---------------+
   | unit_id     |-----+                     +---|  renter_id    |
   | description |     |                     |   |  name         |
   +-------------+     |                     |   |  address      |
                       |                     |   +---------------+
                       |                     |
   +-------------+     |  +------------+     |
   |  calendar   |     |  |  rental    |     |
   +-------------+     |  +------------+     |
   | yearmth     |---+ |  | renter_id  |>----+    +--------------+
   +-------------+   | +-<| unit_id    |          |  payment     |
                     |    | rental_id  |-----+    +--------------+
                     +---<| ym_start   |     |    | payment_id   |
                          | ym_end     |     +---<| rental_id    |
                          | pay_pm     |          | pay_date     |
                          +------------+          | amount       |
                                                  +--------------+
   

The code below sets up some test tables and gives you a couple of queries to demonstrate its implementation.

 

<?php

$dsn   = "mysql:dbname=test; host=127.0.0.1";

$db = new pdo($dsn, $username,$password, 
    [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_EMULATE_PREPARES => false,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC
    ]);


//******************************************
// FUNCTION TO TEST QUERIES               **
//                                        **
//******************************************
    
function sql2html(pdo $db, $sql, $params=[])
{
    $stmt = $db->prepare($sql);
    $stmt->execute($params);
    $out = "<table border='1' style='border-collapse:collapse'>\n";
    $row = $stmt->fetch();
    $out .= "<tr><th>" . join('</th><th>', array_keys($row)) . "</th></tr>\n";
    do {
        $out .= "<tr><td>" . join('</td><td>', $row) . "</td></tr>\n";
    } while ($row  = $stmt->fetch());
    $out .= "</table>\n";
    return $out;
} 

//******************************************
// CREATE TABLES AND INSERT TEST DATA     **
//                                        **
//******************************************

$db->exec("DROP TABLE IF EXISTS unit");
$db->exec("DROP TABLE IF EXISTS renter");
$db->exec("DROP TABLE IF EXISTS rental");
$db->exec("DROP TABLE IF EXISTS payment");
$db->exec("DROP TABLE IF EXISTS calendar");
$db->exec("
       CREATE TABLE calendar (
       yearmth int not null primary key
       )
      ");
$db->exec ("
      CREATE TABLE unit (
      unit_id int not null auto_increment primary key,
      owner_id int,
      description varchar(50)
      )
     ");

$db->exec ("
      CREATE TABLE renter (
      renter_id int not null auto_increment primary key,
      name varchar(50),
      address varchar(255),
      email varchar(150)
      )
     ");

$db->exec ("
      CREATE TABLE rental (
      rental_id int not null auto_increment primary key,
      unit_id int,
      renter_id int,
      ym_start int,
      ym_end int,
      pay_pm int
      )
     ");

$db->exec ("
      CREATE TABLE payment (
      payment_id int not null auto_increment primary key,
      rental_id int,
      pay_date date,
      amount int
      )
     ");

$db->exec("
      INSERT INTO unit (owner_id,description)
      VALUES
      (1, 'Unit 1'),(2, 'Unit 2'), (2, 'Unit 3')
     ");

$db->exec("
      INSERT INTO renter (name,address,email)
      VALUES
      ('A', '25 Letsby Avenue', ''),
      ('B', '2 Effin Close', ''),
      ('C', '123 Any Street', '')
     ");

$db->exec("
      INSERT INTO rental (unit_id, renter_id, ym_start, ym_end, pay_pm)
      VALUES
      (1, 1, 201707, 201806, 50),
      (2, 2, 201701, 201712, 60),
      (3, 3, 201801, 201812, 70)
     ");
     
$db->exec("
          INSERT INTO payment (rental_id, pay_date, amount)
          VALUES
          (1 , '2017-07-01', 150),
          (1 , '2017-10-03', 150),
          (2 , '2017-01-15', 60),
          (2 , '2017-02-15', 60),
          (2 , '2017-03-15', 60),
          (2 , '2017-04-15', 60),
          (2 , '2017-05-15', 60),
          (2 , '2017-06-15', 60),
          (2 , '2017-07-15', 60),
          (2 , '2017-08-15', 60),
          (2 , '2017-09-15', 60),
          (2 , '2017-11-15', 30)
         ");

$stmt = $db->prepare("INSERT INTO calendar VALUES(?)");
for ($y=2016; $y<=2019; $y++) {
    for ($m=1; $m<=12; $m++) {
        $stmt->execute([$y*100 + $m]);
    }
}


echo sql2html($db, "SELECT * FROM rental");

//********************************************
// QUERY TO SHOW RENTALS IN ARREARS         ** 
//********************************************
$sql = "
       SELECT due.rental_id
            , due.description
            , due_to_date
            , paid_to_date
            , IFNULL(paid_to_date, 0) - due_to_date as diff
            , CASE 
                  WHEN due_to_date > IFNULL(paid_to_date, 0) THEN 'Arrears'
                  ELSE ''
              END as note
       FROM
            (
            SELECT r.rental_id
                 , u.description
                 , SUM(pay_pm) as due_to_date
            FROM rental r
                   INNER JOIN 
                 unit u ON r.unit_id = u.unit_id
                   CROSS JOIN
                 calendar c ON c.yearmth BETWEEN r.ym_start AND r.ym_end
                            AND c.yearmth < 201801
            GROUP BY r.rental_id
            ) as due
            LEFT JOIN
            (
            SELECT rental_id
                 , SUM(amount) as paid_to_date
            FROM payment
            GROUP BY rental_id
            ) as paid
            ON due.rental_id = paid.rental_id 
       ";
       
echo sql2html($db, $sql);

//***********************************************
// QUERY TO SHOW PAYMENTS FOR SELECTED RENTAL  **
//***********************************************

$rid = 2;

$sql2 = "
            SELECT r.rental_id
                 , u.description
                 , ROUND(yearmth/100,0) as year
                 , MOD(yearmth, 100) as month
                 , pay_pm as amount_due
                 , amount as amount_paid
            FROM rental r
                   INNER JOIN 
                 unit u ON r.unit_id = u.unit_id
                   CROSS JOIN
                 calendar c ON c.yearmth BETWEEN r.ym_start AND r.ym_end
                            AND c.yearmth < 201801
                   LEFT JOIN
                 payment p ON r.rental_id = p.rental_id
                           AND c.yearmth = EXTRACT(YEAR_MONTH FROM pay_date)
            WHERE r.rental_id = ? 
            ORDER BY year, month
        ";

echo sql2html($db, $sql2, [$rid]);
?>

[CyberShot]

That is interesting. Thank you. I will have to study this

[MeOnTheWeb]

Old post, but thought I'd leave this here for others. When I was setting up my DB, I was trying to do things on paper - doesn't work. Found this site - https://www.dbdesigner.net/ - which is free, and if you need more it's still very inexpensive. Allows you to create your DB and export into actionable SQL statement. It was a lifesaver for me!