|Database Design Advice|
user field or separate table?
| 4:52 pm on Jun 26, 2007 (gmt 0)|
If I had a DB where I needed to track each user's mileage (or some other daily input), how should I design the table/fields? Should I have 1 mileage field for each user on a user table and, with each input, append it to a running comma-separated list? (e.g. 06/25/07:23.5, 06/26/07:19.2, etc.) Or, should I have another separate table joining user ids, date, and a mileage entry?
It seems like the latter is correct, but unwieldy with a few thousand users inputting values daily.
Maybe I just don't realize how big some mySQL tables get on other sites.
Thanks for any insights.
| 3:48 pm on Jun 27, 2007 (gmt 0)|
You may want other miscellaneous fields also (like Comments or Description for the Mileage table), but that's more application-dependent.
| 5:52 pm on Jun 27, 2007 (gmt 0)|
Thanks for the response. Well, I get how to design the tables. I guess my real question is how many rows is too many for a table? Is it better to store an array in one field or break it out into another table? Can a table reach a maximum number of rows or do you just see slower performance as the table grows?
| 6:02 pm on Jun 27, 2007 (gmt 0)|
How many are you thinking is "too many" in your question? We regularly work with several tables with more than 10 million records each.
If it's a question between stuffing more and more records in a table or more and more data into a field, I'll always vote for records unless you can give me a very compelling argument otherwise. Besides, you're going to hit any upper limit for data in a field long before you reach any similar upper limit for number of records.
| 8:06 pm on Jun 27, 2007 (gmt 0)|
That's exactly the answer I was looking for. I suspected as much but I thought I saw an upper limit of ~65,000 records in some documentation before (seems a bit low). I didn't want to paint myself in a corner design-wise. Thanks very much!
| 8:36 pm on Jun 27, 2007 (gmt 0)|
Limits will depend on the DB type and version. A limit of 65,000 records sounds suspiciously like Excel- certainly not a robust DB!