Re: JOIN column maximum

On 6 Jan 2012, at 22:56, Lee Hachadoorian wrote:

> What I am working with is called the summary file, which presents the data in aggregate. That means you have an income table with 16 income classes, plus a total column.

That could be a table: income class and income.

It may make sense to add a record for a "virtual" class 'TOTAL' there, that incorporates the totals column, although I expect those same numbers could be obtained by adding up the numbers in the other categories. If those numbers are rounded though, then your totals can be off a bit that way (due to lack of precision).

> Then you have 9 more tables which show the same income classes by 9 racial and ethnic categories, for a total of 153 more columns.

That could be a table: income class (FK), ethnic category and income.

You could turn these into 9 separate views with one ethnic category pre-selected in each.

> Then you also have a table which crosses 9 income classes by 5 mobility statuses (living in same house, moved within county, moved within state, etc.) for a total of 55 columns.

Another table: income class (FK), mobility status, income.

> Then you have income classes crossed with sex, income classes crossed with mode of transportation to work, sex crossed with mode of transportation to work, etc.

...etc.

> When all is said and done, you have 23,000 columns.

You can definitely normalise that data much more than you are doing. I also think you may be underestimating your researchers if you think they can't handle mapping these Census sheets (which I'm unfamiliar with) to normalised tables.

You may even find that they can find out more from those normalised tables, as they have the freedom to combine tables that the Census Bureau didn't combine for them. Things like "how much do female Asians make compared to their mobility".

Alban Hertroys

--
The scale of a problem often equals the size of an ego.