database setup question
Posted: Sat Mar 27, 2010 7:49 am
Heya guys,
I have a small question with a hypothetical situation on how to design the database most efficient.
Imagine I have a competition website, where you can only compete against yourself in improving your record during 3 months. each day I sign in and tell my day record. for instance: "today I ate a banana in 30 seconds" data input is time. no other data is inserted. during the use i can review my scores.
Now the question.. how would you suggest to set up the database.
situation 1:
table_users: (so 1 table per user with 93 columns)
user_id
user_name
user_pass
recordday_1
recordday_2
etc etc
situation 2:
table_users: (so 1 table with user data)
user_id
user_name
user_pass
&
table_entries: ( 1 table with mixed user data)
entry_id
user_id (foreignkey)
user_record
user_posted
So the question is which database set up will be faster and better assuming a trillion people will visit this banana website. and they should be able to get their previous data during the 3 months use.
situation 1 with 1 table but more columns (92) and just 1 row per user
situation 2 with 2 tables and loads (90 times) of rows.
I am thinking situation 1 is best to set up the database despite the empty columns since the query (Like select from) only has to look for a user_id and display the userdata , hence only has to pass as many rows as users. situation 2 seems not good since it has to pass 90 times (3 months) more rows and combine the data to match a specific user.
Maybe there is some database guru out here, all I have to work with are my unexperienced brains wink.gif
Cheers! and thanks in advance!
monkeyman
P.s. i am still learning so if you know a thing or two, please let me know
I have a small question with a hypothetical situation on how to design the database most efficient.
Imagine I have a competition website, where you can only compete against yourself in improving your record during 3 months. each day I sign in and tell my day record. for instance: "today I ate a banana in 30 seconds" data input is time. no other data is inserted. during the use i can review my scores.
Now the question.. how would you suggest to set up the database.
situation 1:
table_users: (so 1 table per user with 93 columns)
user_id
user_name
user_pass
recordday_1
recordday_2
etc etc
situation 2:
table_users: (so 1 table with user data)
user_id
user_name
user_pass
&
table_entries: ( 1 table with mixed user data)
entry_id
user_id (foreignkey)
user_record
user_posted
So the question is which database set up will be faster and better assuming a trillion people will visit this banana website. and they should be able to get their previous data during the 3 months use.
situation 1 with 1 table but more columns (92) and just 1 row per user
situation 2 with 2 tables and loads (90 times) of rows.
I am thinking situation 1 is best to set up the database despite the empty columns since the query (Like select from) only has to look for a user_id and display the userdata , hence only has to pass as many rows as users. situation 2 seems not good since it has to pass 90 times (3 months) more rows and combine the data to match a specific user.
Maybe there is some database guru out here, all I have to work with are my unexperienced brains wink.gif
Cheers! and thanks in advance!
monkeyman
P.s. i am still learning so if you know a thing or two, please let me know