Database Design Advice

Forum Moderators: open

Message Too Old, No Replies

Database Design Advice

user field or separate table?

4string

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

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

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

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

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

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!