Re: using tables in design
Posted by:
Bob Field
Date: January 07, 2007 06:33PM
I would create a Persons table with person's ID number, name, etc. Then have a Measurements table, with PersonID, MeasurementType, Measurement, date/time. This latter table might grow to hundreds of millions of rows, but each row would be small, binary, fixed-length information. Primary key would be (PersonID, MeasurementType, Date/Time). A further table would list types of measurements, and have MeasurementType as the primary key.
Your latter notion, that of partionining one or more tables, would be something to consider as the database scales larger, and you'd want to use merge tables or the new partitioning as the mechanism, not simply create hundreds of separate tables. It might not even be necessary if the tables are carefully managed, tuned, optimized, etc.