using tables in design
Posted by: Dan-Ari Feinberg
Date: January 07, 2007 06:17PM

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

Options: ReplyQuote


Subject
Written By
Posted
using tables in design
January 07, 2007 06:17PM
January 07, 2007 06:33PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.