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

Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderators: physics

Databases Forum

    
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!

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