database setup question

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
epictreeclimber
Forum Newbie
Posts: 2
Joined: Sat Mar 27, 2010 7:46 am

database setup question

Post by epictreeclimber »

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
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: database setup question

Post by califdon »

Welcome to DevNetwork Forums and welcome to the wild and wonderful world ("www") of database design.

It's more than an issue of efficiency, it's an issue of data normalization to meet the requirements of the "relational model" that is the basis of all relational database functioning and on which the SQL language is founded.

Your first schema is unnormalized because it has "repeating columns" and therefore doesn't meet the requirements of the relational model. It will work, after a fashion, for the simplest of queries, but will not facilitate generalized SQL queries.

I strongly recommend that you learn about the principles of relational databases. There are lots of tutorials online, even one that I wrote at http://forums.aspfree.com/microsoft-acc ... 08217.html
DaiLaughing
Forum Commoner
Posts: 76
Joined: Thu Jul 16, 2009 8:03 am

Re: database setup question

Post by DaiLaughing »

Totally agree with califdon. I'll add that i can't see there would be an query-easiness gain either because you would only need to query one table anyway.

When they log on you would use table 1. You would then not need to use that table again. Instead the user would be selecting personal records (assuming they can have more than just eating bananas) from Table 2 and inputting data to it.
epictreeclimber
Forum Newbie
Posts: 2
Joined: Sat Mar 27, 2010 7:46 am

Re: database setup question

Post by epictreeclimber »

Thanks alot guys!
Post Reply