Page 1 of 2

Is 90 columns too much?

Posted: Wed Aug 01, 2007 2:17 pm
by WorldCom
Is having 90+ columns too much for one table?

This is actually for an NFL script for Win, Lose, Tie with 32 teams ( 32 * 3 ).

Just wondering if that puts a lot of strain on the DB.

Posted: Wed Aug 01, 2007 2:21 pm
by dhrosti
why not use teamname, win, lose, tie as columns and just have 90+ rows?

Posted: Wed Aug 01, 2007 2:34 pm
by nathanr
yep, just what the pirate from leeds said

Posted: Wed Aug 01, 2007 2:35 pm
by alex.barylski
I guess anything is possible but 90 columns is waaaaaaaaaaaaaaaaaaaay to much IMHO.

You end up with a SQL story instead of a SQL statement. :P

Like functions which are massive and begging to be refactored into more reusable smaller functions, tables are similar, in that you want to try and reduce duplication.

How does your table stack up against normalization practices?

http://en.wikipedia.org/wiki/Database_normalization

Posted: Wed Aug 01, 2007 2:35 pm
by MrPotatoes
not really. depends on how much of that you can normalize.

over here we have a table that is about 200 columns and hundreds of thousands of rows. works just fine.

no, it cannot be normalized. you gotta love healthcare. there is barely any room to do what you'd like :(

Posted: Wed Aug 01, 2007 2:42 pm
by nathanr
MrPotatoes, can't you normalise and give them the tables they require as views instead? totally of topic but just wanted to throw that in!

back to the matter in hand, 90 columns+ is fine, no strain on the db server, strain on your sql query writing though.. I feel that normalisation would definately help you in this case, and certainly stand you in good stead for future projects :)

Posted: Wed Aug 01, 2007 3:26 pm
by s.dot
32 rows with 3 columns each would be much more normal and practical.
Try to make your data as practical and as normalised as you can. This will help you out tons when/if your scripts grow and the scalability factor comes into play.

Posted: Wed Aug 01, 2007 3:28 pm
by WorldCom
dhrosti wrote:why not use teamname, win, lose, tie as columns and just have 90+ rows?
I would have done it that way, but the idea is there is a history being developed for each year.
So my table will have:

| year | team_1_win | team_1_loss | team_1_tie | team_2_win | team_2_loss | team_2_tie |. . .

Whereas the year is the primary key.
So what I'm getting is this is not a good idea.

I'm not sure what normalizing is ???

Would 3 tables be better?

table.win
table.loss
table.tie

Posted: Wed Aug 01, 2007 3:35 pm
by s.dot
1 table with the following fields

`id`, `teamname`, `year`, `win`, `loss`, `tie`

With a separate record being inserted for each year of each team

Posted: Wed Aug 01, 2007 3:37 pm
by onion2k
WorldCom wrote:
dhrosti wrote:why not use teamname, win, lose, tie as columns and just have 90+ rows?
I would have done it that way, but the idea is there is a history being developed for each year.
So my table will have:

| year | team_1_win | team_1_loss | team_1_tie | team_2_win | team_2_loss | team_2_tie |. . .

Whereas the year is the primary key.
So what I'm getting is this is not a good idea.

I'm not sure what normalizing is ???

Would 3 tables be better?

table.win
table.loss
table.tie
I'd use 1 table..

game_id
home_team_id
visitor_team_id
home_score
visitor_score
game_date

If you needed the number of home wins in a season that a team had achieved you'd do something like:

Code: Select all

SELECT COUNT(game_id) FROM `games` WHERE home_score > visitor_score AND YEAR(game_date) = 2007;
Of course, selecting all the wins, or all the wins, draws and loses in a single query is pretty tricky, but you could always cache those into the team table for quicker referencing ... maybe have them update via a trigger that fires every time something is inserted into the games table.

Posted: Wed Aug 01, 2007 3:51 pm
by WorldCom
Now I'm really thinking.
What onion2k said I may already have in place.

Already have table with

game_id ( primary key )
season ( year )
week ( 1 - 17 )
plus
32 columns for Away and Home team for each week which are 1 to 32 to represent each team and who they are playing.

So every year will have 17 rows to represent all games.

The scores are in a similar table with the game_id linking the 2 tables.

I guess ....... somehow ... I can cycle through each time to get the info I need.
My brain is already aching ..... hehe

Posted: Wed Aug 01, 2007 4:46 pm
by webgroundz
i agree with the dhrosti pirate say! :D

you can list all the 32 teams in 1 column namely team_name, it is dynamic because if ever they added new team you don't have to add another column for the teams.

:wink: :wink: :wink:

Posted: Wed Aug 01, 2007 6:53 pm
by ReverendDexter
WorldCom wrote:Now I'm really thinking.
What onion2k said I may already have in place.

Already have table with

game_id ( primary key )
season ( year )
week ( 1 - 17 )
Everything up to here is great
WorldCom wrote: plus
32 columns for Away and Home team for each week which are 1 to 32 to represent each team and who they are playing.

So every year will have 17 rows to represent all games.

The scores are in a similar table with the game_id linking the 2 tables.
This is where it totally goes downhill :) Just make a row for each game. In each row, you'll have the game_id, the year, the week, the home team, the away team, the home score, and the away score (win/lose/tie can be derived from these).

Using this setup, you can get win/loss records for a team, you can see how a team fared across a season (or all seasons), you can see what their average is against another specific team... all fairly easily; try generating those queries on your 40+ column table!


The flipside of this, what information will you want to get OUT of this database?

Posted: Wed Aug 01, 2007 7:19 pm
by WorldCom
This is where it totally goes downhill :) Just make a row for each game. In each row, you'll have the game_id, the year, the week, the home team, the away team, the home score, and the away score (win/lose/tie can be derived from these).

Using this setup, you can get win/loss records for a team, you can see how a team fared across a season (or all seasons), you can see what their average is against another specific team... all fairly easily; try generating those queries on your 40+ column table!


The flipside of this, what information will you want to get OUT of this database?
Well ATM with what I have, each Week has an specific unique id.
With your system, by the end of the year, I would have 16 games per week x 17 weeks = 272 rows for each year.
Now I just have one row in each of 3 tables to maintain all score information, dates, times teams etc.

The user picks' are in yet a separate table linked to the game_id and the user_id and that would have 17 rows per user, per season.

Ok, why I was asking at first was, the users' picks are updated, points are added and games closed after being played. The win, loss, tie are really just viewable stats and I've already updated any information I needed to. What I was thinking ...... when I close out the game, update the user, I would just add in the outcome for each team as this process is going on.

That's why I wondered if the 90 columns was a huge overhead, rather than querying the 2 tables for the same stats. As soon as that information is in the table(or tables), it's just read back (one array) and displayed.

I do appreciate the feed back though ;)

Posted: Thu Aug 02, 2007 3:04 am
by onion2k
Having all the games for a single week in one row with lots of columns means you can't. You're limited to pulling out all the data at once for any given week. By having 1 row per game you can select any individual game very easily (where game_date = '2007-08-02' and home_team_id = 1 and visitor_team_id = 2), or all the games for a given week (where game_date > '2007-07-26' and game_date <= '2007-08-02') , or all the games where a particular team have played this year (where (home_team_id = 1 or visitor_team_id = 1) and YEAR(game_date) = 2007), or any game played on a Wednesday where the number of goals is more than 10 (where DAYOFWEEK(game_date) = 4 and home_score+visitor_score > 10) ... anything you like really. It doesn't take a lot of imagination just how much power that gives you over your web site.

Taking a 'real world' example, imagine if you had 15 years worth of data and you wanted to fetch the name of the winning team for all the times team 1 and team 2 had met previously. How would you do that using your system of 1 row with a whole week of data in it? You'd have to fetch all the data out of the database and use PHP to figure out what's the relevant. If you had 1 row per game you could do:

Code: Select all

SELECT 
IF (home_score>visitor_score, home_team_id, visitor_team_id) AS winning_team_id 
FROM `games`
WHERE 1 
AND (home_team_id = 1 AND visitor_team_id = 2)
OR (home_team_id = 2 AND visitor_team_id = 1)
That's it. You'd have a list of all the previous games winners.