Welcome to WebmasterWorld Guest from 54.234.38.8

Forum Moderators: open

Message Too Old, No Replies

Database Design Advice

user field or separate table?

     
4:52 pm on Jun 26, 2007 (gmt 0)

Full Member

10+ Year Member

joined:Mar 28, 2004
posts:224
votes: 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 June 27, 2007 (gmt 0)

Moderator from US 

WebmasterWorld Administrator lifeinasia is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month

joined:Dec 10, 2005
posts:5550
votes: 24


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.

5:52 pm on June 27, 2007 (gmt 0)

Full Member

10+ Year Member

joined:Mar 28, 2004
posts:224
votes: 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 June 27, 2007 (gmt 0)

Moderator from US 

WebmasterWorld Administrator lifeinasia is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month

joined:Dec 10, 2005
posts:5550
votes: 24


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 June 27, 2007 (gmt 0)

Full Member

10+ Year Member

joined:Mar 28, 2004
posts:224
votes: 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 June 27, 2007 (gmt 0)

Moderator from US 

WebmasterWorld Administrator lifeinasia is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month

joined:Dec 10, 2005
posts:5550
votes: 24


Limits will depend on the DB type and version. A limit of 65,000 records sounds suspiciously like Excel- certainly not a robust DB!