Re: Extremely large tables

[Malcolm Wardlaw]

The queries will be quite simple. I suspect the majority of them will be simple data requests for specific columnar data, conditional on the company id and time variables. With the data vertically partitioned they'll be a number of 1-to-1 joins. Additionally they'll be some 1-to-many joins based on date ranges for the company data. I've been told that for this type of thing, the databases are better of somewhat de-normalized.

I'm a little new at large scale database design so I don't know if it's optimal to index the whole table by company id and date, to generate a separate key, or to simply have a separate tables indexed by company id for each date. Given that I'll probably have to vertically partition the tables, I guess I'll have to have an index table for column locations as well.

It's somewhat likely that I will never write to any of the databases, and if I do, it will only be one or two times a year, max.

One more thing that might be relevant. I want to draw all of this data via a stats program, probably Stata, either with ODBC or a plugin.

Any advice?