How to prevent updated data in db to intervere with other tables? [closed]

Closed. This question is opinion-based. It is not currently accepting answers.

---

Want to开发者_StackOverflow improve this question? Update the question so it can be answered with facts and citations by editing this post.

Closed 2 years ago.

Improve this question

I'm working on a php project to make a task schedule.

Companies have to sent a VAT declaration to the IRS each Vat-period. Some companies do this once a month and other companies do this once each quarter.

In my company table I use a vatperiod_id to determine how often the company has to sent a declaration. When I add a company the tasks will automatic be generated for the whole year. No problem so far. But sometimes the vat-period changes during a year.

Historic tasks which are saved in the db should not be modified, but only future tasks (which also have been generated by the script) should use the updated vat-period_id.

Example: I add Company X with vatperiod_id 2 (declarations each quarter) and my script stores for month (4, 7, 10 and month 1 of the following year) a task. Now lets say the vatperiod_id changes in July to vatperiod_id 3 (declarations each month). The tasks generated, for month 10 and 1 of the following year, should be deleted and replaced by a new task in each following month.

(It's quite difficult to explain)

How to solve this problem? Which data should I store in a db?

---

Here is my attempt

• One row in the Calendar table is one day. The table is used to simplify handling of dates. Usually, you would pre-load this for the next 20 years, or so.

• The Company table also holds the current reporting period type (month, quarter, year..)

• TaxSchedule is pre-populated based on the ReportingPeriodType. One option is to pre-populate this table with the finest grain (month) even if the company reports every quarter or year.

• One row in the Declaration table holds a return as submitted by a company.

• Schedule_Declaration is where declarations are matched against the schedule. One schedule can match several declarations (schedule = quarter, but declarations = monthly). One declaration can match several schedules (schedule = month, but declaration = quarter; late filing). Any matching is done on the application layer.

---

Instead of generating future tasks/operations I now use a PHP function to verify which companies have to submit a VAT-declaration.

When I want to have an overview of companies that are obligated to submit a VAT-declaration in March (=example). I simply can check the company table for the companies where vatperiod_id=3 (companies that have to hand in VAT-declarations each month).

Only when an Tax-advisor "accepts" a task, the task will be stored in the database and the task-owner can alter the status.

function showVatOperations($month) {
    //declare variables
    global $mysqli;

    $query = "SELECT * FROM `company` 
              JOIN `vat_period` ON company.vatperiod_id = vat_period.vatperiod_id
              JOIN `owner` ON company.owner_id = owner.owner_id
              WHERE company.vatperiod_id = 3";

    //check for each period which companies have to submit a declaration
    switch ($month) {
        case "1":
            $query.=" OR company.vatperiod_id = 2 OR company.vatperiod_id = 1";
            break;
        case "4":
            $query.=" OR company.vatperiod_id = 2";
            break;
        case "7":
            $query.=" OR company.vatperiod_id = 2";
            break;
        case "10":
            $query.=" OR company.vatperiod_id = 2";
            break;
    }

    //perform query to select company and vatoperation info
    $result = $mysqli->query($query) or die("ERROR $query. $mysqli->error()");

    showVatOperationDetails($result);

}
?>