| Database Design Advice
user field or separate table? |
4string
msg:3379345 | 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.
|
LifeinAsia
msg:3380360 | 3:48 pm on Jun 27, 2007 (gmt 0) |
Basic framework: Table Users:
UserID
UserName Table Mileage:
UserID
MialeageDate
Mileage You may want other miscellaneous fields also (like Comments or Description for the Mileage table), but that's more application-dependent.
|
4string
msg:3380468 | 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?
|
LifeinAsia
msg:3380481 | 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.
|
4string
msg:3380569 | 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!
|
LifeinAsia
msg:3380591 | 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!
|
|
|
