Is 90 columns too much?

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

Moderator: General Moderators

WorldCom
Forum Commoner
Posts: 45
Joined: Sat Jun 24, 2006 8:14 am
Location: Ontario, Canada

Is 90 columns too much?

Post 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.
User avatar
dhrosti
Forum Commoner
Posts: 90
Joined: Wed Jan 10, 2007 5:01 am
Location: Leeds, UK

Post by dhrosti »

why not use teamname, win, lose, tie as columns and just have 90+ rows?
User avatar
nathanr
Forum Contributor
Posts: 200
Joined: Wed Jun 07, 2006 5:46 pm

Post by nathanr »

yep, just what the pirate from leeds said
alex.barylski
DevNet Evangelist
Posts: 6267
Joined: Tue Dec 21, 2004 5:00 pm
Location: Winnipeg

Post 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
User avatar
MrPotatoes
Forum Regular
Posts: 617
Joined: Wed May 24, 2006 6:42 am

Post 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 :(
User avatar
nathanr
Forum Contributor
Posts: 200
Joined: Wed Jun 07, 2006 5:46 pm

Post 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 :)
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

Post 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.
Set Search Time - A google chrome extension. When you search only results from the past year (or set time period) are displayed. Helps tremendously when using new technologies to avoid outdated results.
WorldCom
Forum Commoner
Posts: 45
Joined: Sat Jun 24, 2006 8:14 am
Location: Ontario, Canada

Post 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
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

Post 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
Set Search Time - A google chrome extension. When you search only results from the past year (or set time period) are displayed. Helps tremendously when using new technologies to avoid outdated results.
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Post 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.
WorldCom
Forum Commoner
Posts: 45
Joined: Sat Jun 24, 2006 8:14 am
Location: Ontario, Canada

Post 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
User avatar
webgroundz
Forum Commoner
Posts: 58
Joined: Thu Jun 21, 2007 1:20 am
Location: Philippines

Post 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:
User avatar
ReverendDexter
Forum Contributor
Posts: 193
Joined: Tue May 29, 2007 1:26 pm
Location: Chico, CA

Post 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?
WorldCom
Forum Commoner
Posts: 45
Joined: Sat Jun 24, 2006 8:14 am
Location: Ontario, Canada

Post 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 ;)
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Post 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.
Post Reply