Is 90 columns too much?
Moderator: General Moderators
Is 90 columns too much?
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.
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.
-
alex.barylski
- DevNet Evangelist
- Posts: 6267
- Joined: Tue Dec 21, 2004 5:00 pm
- Location: Winnipeg
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.
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
You end up with a SQL story instead of a SQL statement.
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
- MrPotatoes
- Forum Regular
- Posts: 617
- Joined: Wed May 24, 2006 6:42 am
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
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
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.
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.
I would have done it that way, but the idea is there is a history being developed for each year.dhrosti wrote:why not use teamname, win, lose, tie as columns and just have 90+ rows?
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
1 table with the following fields
`id`, `teamname`, `year`, `win`, `loss`, `tie`
With a separate record being inserted for each year of each team
`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.
I'd use 1 table..WorldCom wrote:I would have done it that way, but the idea is there is a history being developed for each year.dhrosti wrote:why not use teamname, win, lose, tie as columns and just have 90+ rows?
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
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;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
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
- webgroundz
- Forum Commoner
- Posts: 58
- Joined: Thu Jun 21, 2007 1:20 am
- Location: Philippines
- ReverendDexter
- Forum Contributor
- Posts: 193
- Joined: Tue May 29, 2007 1:26 pm
- Location: Chico, CA
Everything up to here is greatWorldCom 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 )
This is where it totally goes downhillWorldCom 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.
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.This is where it totally goes downhillJust 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?
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
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:
That's it. You'd have a list of all the previous games winners.
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)