Optimize filing_filing table definition

[cschnaars]

Best I can tell, the filing_filing table is not automatically generated and could be optimized to get rid of unused columns and trim variable-length character fields. My findings are based only on looking at 1.6 million filings from 2014 through early 2018, so it's possible larger fields were used in the past or should be left in place to allow for future growth. Also, many fields should be non-nullable.

• return_id (varchar (31)): This fields is never NULL, is always 8 characters long and never begins with a zero, so it could be converted to char (8) or even int.
• filing_type (varchar (31)): This field is never NULL and is always populated with: "EFILE" Reduce to char (5) or drop the field entirely.
• ein (varchar (31)): This field is never NULL and is always 9 characters. Convert to char (9). Also, the definition lacks the help_text property, which could be set to: "Employer Identification Number"
• sub_date (varchar (31)): Sometimes this field includes a time. It would be more useful if it was parsed as datetime; if that's not practical, reduce to varchar (22). It is never NULL.
• taxpayer_name (varchar (255)): This field is never NULL and never exceeds 100 characters. Convert to varchar (100).
• return_type (varchar (7)): Never NULL. Reduce to varchar (5).
• dln (varchar (31)): Model says this field is "download number," but I think it's actually: "Document Locator Number" and is an amalgamation of seven codes. (Some documentation here: https://www.irs.gov/pub/irs-utl/6209-section4-2012.pdf) This field is never NULL and is always 14 characters long. Change to char (14).
• object_id (varchar (31)): This field is never NULL and is always 18 characters long. It's also always unique and could be used in lieu of adding the id field as a primary key, though populating this table is probably much faster by adding our own key, if this table is indexed.
• schema_version (text): This field is always NULL. I think it would be really useful if the code populated this field. If not, we should drop it or at least reduce the size. I think varchar (8) would be sufficient.
• tax_year (int): This field also would be useful (though not as much as schema_version), but it's always NULL. This field should either be populated or dropped.
• parse_started (bool), parse_complete (bool) and process_time (timestamp): These are all populated and useful during import, but not so much after that. Because I think it's useful to make this table as lean as possible, maybe move these fields to another table? As I understand the structure of the database so far, this table could be the parent for all database queries, and so the leaner, the better.
• is_error (bool), key_error_count (int) and error_details (text): These fields are always NULL. If they are not being used, they should be dropped. If they're still needed, consider moving them to a separate table with the other three fields used during import.

[jsfenfen]

Good points, I'm not super concerned about field size for varchars postgres is pretty efficient. This is one of the smaller tables in the db, optimizing this and then loading 10 gigs on other tables seems not incredibly useful. Closing and reopening the relevant bits.

[jsfenfen]

Also @cschnaars: filing_filing should be created by a migration. You might need to generate and run that. That should be step 1 here.

[jsfenfen]

Split into #5 #6 and #7