Add Housing Growth tables to flow

[dhochbaum-dcp]

More info regarding the tables can be found here.

[pratishta]

@dhochbaum-dcp I talked to Damon and he suggested we add a column to the community district table that has the actual composite key (e.g. composite_borough_cd_id = '101') so that the singular housing table can reference that with geoid (and geotype = 'cd'). We also talked about adding a column for version in the housing table so that if we have an update, we don't lose previous data.

[dhochbaum-dcp]

So after our conversation, I looked into this a bit, and the understanding that I came away with is that the value in using foreign keys is to maintain the integrity of the data when your code is performing additions/deletions/updates. But we aren't performing any of those operations outside of the data flow, and the set of Community District ids doesn't change.

Separate tables is generally better for data integrity, and is faster than having a single table with an indicator field when querying a single subset (as we will always be doing).

I think that we should stick with separate tables, although I'm still open to more discussion if you or @damonmcc disagrees.

I'm open to adding the borough+cd id field to the CDs table, although if we go with separate tables I would break that out separately, since it's not necessary for completing the Housing Growth.

I think adding a column for version is a great idea.

[damonmcc]

@dhochbaum-dcp the separate tables you're talking about are separate housing_growth_cd and housing_growth_nta tables? just wanna make sure I'm following

[dhochbaum-dcp]

@dhochbaum-dcp the separate tables you're talking about are separate housing_growth_cd and housing_growth_nta tables? just wanna make sure I'm following

Yes