Msg#: 3379343 posted 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.
Msg#: 3379343 posted 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?
Msg#: 3379343 posted 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.
Msg#: 3379343 posted 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!