homepage Welcome to WebmasterWorld Guest from
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member

Visit PubCon.com
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

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)

Basic framework:

Table Users:

Table 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 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!

Global Options:
 top home search open messages active posts  

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved