Tax status code utility table

[edarsow]

Dear SNAPPERVIPER,

The Tax status of a property is so important that it deserves more infrastructure in the database to facilitate assigning a permanent classification of "tax status" to a property. There are two approaches that come to mind:

Design ideas

1. Attach a tax status field to the property table such that each property has a single current tax status indicator that becomes part of the master property record. This would probably involve creating a utility table whose records correspond to a single discrete tax status, such as "unpaid-unverified", "unpaid-verified", "current", etc.
2. Instead of attaching tax status directly to the property, we instead assess the tax status with each external data update, in which case we'd create a foreign key field on the current 'propertyexternaldata` table that links to the utility table described above.

The advantage of the first approach is it's less ambiguous from an interpretation standpoint, since the second approach involves assessing a series of scraped status. The second approach allows us to experiment with the classification scheme without touching the highly sensitive property table.

Tasks

1:DB status table

Use existing status tables as a guide, such as propertystatus, to create a script that births a new table

We might also think about just using the propertystatus table and include tax status records in there alongside the other classifications, such as "large-scale rental".

3: Populate table

Write a simple set of INSERT statements to load up the status table with sensible initial values

4: Link to status table

Create corresponding foreign key columns in either the property or propertyexternaldata table based on your design. Write a script to make the links.

5: Logic script

Write a python function/module that can interpret the scraped data about taxes and assign a sensible status code to the record. As usual, append any necessary notes to the notes field to capture a record of how the script determined tax status, by writing a note such as "found UNPAID in county scrape"

6: Test like hell

Squash bugs!

[snapperVibes]

Edited tax status design proposal:

-- Tax status table. Only scraped data goes here.

create table if not exists taxstatus(
    taxstatusid         int primary key, -- sequence
    year                int,
    paidstatus          text,
    tax                 decimal,
    penalty             decimal,
    interest            decimal,
    total               decimal,
    datepaid            date
);

Taxcode (as given by the WPRDC) belongs as an attribute to property. alter table property add column taxcode char(1);

propertyexternaldata will have a reference to taxstatus_taxstatusid. property will not reference taxstatus because changes to property require a code officer's input.

Changes to propertyexternaldata

alter table propertyexternaldata
   add column taxstatus_taxstatusid int references taxstatus(taxstatusid),
   drop column tax,
   drop column taxsubcode,  -- Does not seem important but there's no harm to keeping it if you want
   drop column taxstatus,
   drop column taxstatusyear;
   -- we do not drop taxcode as changes are notable but need code officer approval on property

I've updated most of the code necessary for these changes to work with my scripts. I am hoping for design input before I get the go-ahead to make changes on the production server.