using tables in design
I'm trying to learn some basic database design, and I'm hoping someone can help me by commenting on the following theoretical situation. This may be too long a question or too involved an answer for this forum, but I'll give it a shot:
Building a DB to store measurements for about 100,000 people. There will be an average of 100 measurements for each person. A measurement consists of the DATETIME the measurement was made, and the person's weight as an unsigned SMALLINT.
After talking to our theoretical users, we know that the likely use of the DB is to get all (or part) of the measurements for a particular person, so this is what we should design for. It is highly unlikely that the users will want to get all of the measurements taken on a particular day regardless of person, and if they ask for that, it is OK for this query to be very, very slow.
So, here's my design:
Each person gets an automatically incremented PersonIDKey. It would seem that the easiest thing to do would be to create a table with PersonIDKey as the key column, and a column of references to tables to hold measurements. Each of these tables (one per person) would have one column for DATETIME and one column for the weight measurement (unsigned SMALLINT). This makes it very easy (and I think, quick) to retrieve all measurements for a particular person. From what I've been able to read, making this many tables (one for each person, plus the first table) might be a bad thing, with the possibility of DB corruption. Is this a bad design?
The other possibility is to make one table. First column is a measurement number as a key column and then a column for PersonIDKey, and then a column for the DATETIME of when the measurement was made and a column for the measurement. Seems simpler, and it uses only one table, but won't this bog down when the DB grows to millions of measurements? Won't this be slow in trying to query for all measurements for a particular PersonIDKey? Or is this exactly what MySQL is made for?
Or is there a third possibility, with the previous table split into multiple tables, one for each 100,000 measurements, with a new table created when the previous gets full. This might avoid the problem of a single table from growing too big, but to get all measurements for a person, you'd still need to query all the tables, so there seems to be no time savings.
Your thoughts and comments are appreciated. If it is OK is this forum, a pointer to a practical book on DB design to help me understand these kinds of issues would also be appreciated.
Thanks,
Dan