#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2013
    Posts
    6
    Rep Power
    0

    Database-driven Website question


    Hey guys. I'm new to database programming, been reading books lately and have created several desktop applications. I am creating a website in ASP.NET(C#). I want(I think I want) this website to be database-driven as it will hold tons of user information.

    Without explaining too many details of my idea for the website I will explain one part of it in enough detail so you guys can understand my questions.

    I have one database file that contains MANY tables but the ones to note are the following tables:
    "Users"
    "NFLWeek1"
    "NFLWeek1UserPicks"

    The "Users" table contains all the users' information. Column one being "User_ID"(Primary key)

    "NFLWeek1" lists all(16) games in week 1(row 1 being game 1, row 2 being game 2, etc) - the columns for this table include; team1name, team2name, team1score, team2score, winner, etc.

    "NFLWeek1UserPicks" list all the users' picks for each game in that week. Each row being a different user. Columns for this table include; User_ID(foreign key), Winnergame1, game1wage, and several other columns pertaining to game1, THEN Winnergame2, game2wage, etc. etc.... which means this one table is 96 columns wide! That's for only 16 NFL games in a week. If I did the same for MLB, having roughly 100 games a week, my "MLBWeek1UserPicks" table will be roughly 600 columns wide!!

    So here's my question(assuming I haven't bored you yet)... Am I going about this the wrong way??? Would there be a better way of saving this user information??? It just seems to me that this is a clumsy way of storing this information. But I don't have enough database experience to know. Thanks for any input on this guys!! Cheers!
  2. #2
  3. No Profile Picture
    Lost in code
    Devshed Supreme Being (6500+ posts)

    Join Date
    Dec 2004
    Posts
    8,316
    Rep Power
    7170
    moved to database management

    Yes, there is a much better way to design that. You should almost never end up with tables like "NFLWeek1". You also should almost never end up with column names like "game1wage", "game2wage", "game3wage".

    When you have tables like that, you need to consolidate them into a single table and use a column to differentiate between the data. For example, you can consolidate NFLWeek1, NFLWeek2, NFLWeek3, etc. into a single table, NFLWeeks, and add a new column to it called "Week". The Week column contains a value like 1, 2, 3, etc. Now you only have one table for all weeks, instead of a separate table for each week. If you need to get the data for just week 1, then you use a condition in your WHERE clause to accomplish it.

    When you have duplicates column names like that, you should instead split the data into multiple rows and use a new column to distinguish between number. For example, you should have only a winnergame column and a gamewage column, and then you should have a gamenumber column so that you can distinguish between them. Each game will have its own row in the table.
    PHP FAQ

    Originally Posted by Spad
    Ah USB, the only rectangular connector where you have to make 3 attempts before you get it the right way around
  4. #3
  5. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,235
    Rep Power
    4279
    thanks, E-Oreo, great explanation

    yo, dawg, i heard you like nfl

    well, it so happens that i actually run a database-driven nfl pool on my website

    i will share with you the design of the main tables

    this is written in mysql, so since you are a microsoftie, you may have to tweak the sql a bit

    Code:
    CREATE TABLE IF NOT EXISTS `nfl_teams` (
      `teamabbr` char(3) NOT NULL,
      `teamname` varchar(37) NOT NULL,
      `teamnick` varchar(37) NOT NULL,
      PRIMARY KEY  (`teamabbr`)
    );
    INSERT INTO `nfl_teams` (`teamabbr`, `teamname`, `teamnick`) VALUES
    	('ARI', 'Arizona', 'Cardinals'),
    	('ATL', 'Atlanta', 'Falcons'),
    	('BAL', 'Baltimore', 'Ravens'),
    	('BUF', 'Buffalo', 'Bills'),
    	('CAR', 'Carolina', 'Panthers'),
    	('CHI', 'Chicago', 'Bears'),
    	('CIN', 'Cincinnati', 'Bengals'),
    	('CLE', 'Cleveland', 'Browns'),
    	('DAL', 'Dallas', 'Cowboys'),
    	('DEN', 'Denver', 'Broncos'),
    	('DET', 'Detroit', 'Lions'),
    	('GB', 'Green Bay', 'Packers'),
    	('HOU', 'Houston', 'Texans'),
    	('IND', 'Indianapolis', 'Colts'),
    	('JAX', 'Jacksonville', 'Jaguars'),
    	('KC', 'Kansas City', 'Chiefs'),
    	('MIA', 'Miami', 'Dolphins'),
    	('MIN', 'Minnesota', 'Vikings'),
    	('NE', 'New England', 'Patriots'),
    	('NO', 'New Orleans', 'Saints'),
    	('NYG', 'N.Y. Giants', 'Giants'),
    	('NYJ', 'N.Y. Jets', 'Jets'),
    	('OAK', 'Oakland', 'Raiders'),
    	('PHI', 'Philadelphia', 'Eagles'),
    	('PIT', 'Pittsburgh', 'Steelers'),
    	('SD', 'San Diego', 'Chargers'),
    	('SEA', 'Seattle', 'Seahawks'),
    	('SF', 'San Francisco', '49ers'),
    	('STL', 'St. Louis', 'Rams'),
    	('TB', 'Tampa Bay', 'Buccaneers'),
    	('TEN', 'Tennessee', 'Titans'),
    	('WAS', 'Washington', 'Redskins');
    
    
    CREATE TABLE IF NOT EXISTS `nfl_byes` (
      `weekno` tinyint(4) NOT NULL,
      `teamabbr` char(3) NOT NULL,
      PRIMARY KEY  (`weekno`,`teamabbr`)
    );
    INSERT INTO `nfl_byes` (`weekno`, `teamabbr`) VALUES
    	(4, 'IND'),
    	(4, 'PIT'),
    	(5, 'DAL'),
    	(5, 'DET'),
    	(5, 'OAK'),
    	(5, 'TB'),
    	(6, 'CAR'),
    	(6, 'CHI'),
    	(6, 'JAX'),
    	(6, 'NO'),
    	(7, 'ATL'),
    	(7, 'DEN'),
    	(7, 'KC'),
    	(7, 'MIA'),
    	(7, 'PHI'),
    	(7, 'SD'),
    	(8, 'BAL'),
    	(8, 'BUF'),
    	(8, 'CIN'),
    	(8, 'HOU'),
    	(9, 'NE'),
    	(9, 'NYJ'),
    	(9, 'SF'),
    	(9, 'STL'),
    	(10, 'ARI'),
    	(10, 'CLE'),
    	(10, 'GB'),
    	(10, 'WAS'),
    	(11, 'MIN'),
    	(11, 'NYG'),
    	(11, 'SEA'),
    	(11, 'TEN');
    
    CREATE TABLE IF NOT EXISTS `nfl_games` (
      `gameno` smallint(6) NOT NULL,
      `weekno` tinyint(4) NOT NULL,
      `gamedate` datetime NOT NULL,
      `vteam` char(3) NOT NULL,
      `hteam` char(3) NOT NULL,
      `spread` decimal(4,1) NOT NULL default '0.0',
      `vscore` tinyint(4) NOT NULL default '0',
      `hscore` tinyint(4) NOT NULL default '0',
      `OT` tinyint(4) NOT NULL default '0',
      PRIMARY KEY  (`gameno`)
    );
    INSERT INTO `nfl_games` (`gameno`, `weekno`, `gamedate`, `vteam`, `hteam`, `spread`, `vscore`, `hscore`, `OT`) VALUES
    	(1, 1, '2012-09-05 20:30:00', 'DAL', 'NYG', 3.5, 24, 17, 0),
    	(2, 1, '2012-09-09 13:00:00', 'NE', 'TEN', -6.5, 34, 13, 0),
    	(3, 1, '2012-09-09 13:00:00', 'PHI', 'CLE', -8.5, 17, 16, 0),
    	(4, 1, '2012-09-09 13:00:00', 'WAS', 'NO', 9.5, 40, 32, 0),
    	(5, 1, '2012-09-09 13:00:00', 'MIA', 'HOU', 10.5, 10, 30, 0),
    	(6, 1, '2012-09-09 13:00:00', 'ATL', 'KC', -2.5, 40, 24, 0),
    	(7, 1, '2012-09-09 13:00:00', 'JAX', 'MIN', 4.5, 23, 26, 1),
    	(8, 1, '2012-09-09 13:00:00', 'BUF', 'NYJ', 3.5, 28, 48, 0),
    	(9, 1, '2012-09-09 13:00:00', 'STL', 'DET', 8.5, 23, 27, 0),
    	(10, 1, '2012-09-09 13:00:00', 'IND', 'CHI', 9.5, 21, 41, 0),
    	(11, 1, '2012-09-09 16:25:00', 'CAR', 'TB', -2.5, 10, 16, 0),
    	(12, 1, '2012-09-09 16:25:00', 'SEA', 'ARI', -2.5, 16, 20, 0),
    	(13, 1, '2012-09-09 16:25:00', 'SF', 'GB', 5.5, 30, 22, 0),
    	(14, 1, '2012-09-09 20:20:00', 'PIT', 'DEN', 1.5, 19, 31, 0),
    	(15, 1, '2012-09-10 19:00:00', 'CIN', 'BAL', 6.5, 13, 44, 0),
    	(16, 1, '2012-09-10 22:15:00', 'SD', 'OAK', -1.5, 22, 14, 0),
    	(17, 2, '2012-09-13 20:20:00', 'CHI', 'GB', 5.5, 10, 23, 0),
    	(18, 2, '2012-09-16 13:00:00', 'ARI', 'NE', 13.5, 20, 18, 0),
    	(19, 2, '2012-09-16 13:00:00', 'TB', 'NYG', 7.5, 34, 41, 0),
    	(20, 2, '2012-09-16 13:00:00', 'KC', 'BUF', 3.5, 17, 35, 0),
    	(21, 2, '2012-09-16 13:00:00', 'NO', 'CAR', -2.5, 27, 35, 0),
    	(22, 2, '2012-09-16 13:00:00', 'MIN', 'IND', -1.5, 20, 23, 0),
    	(23, 2, '2012-09-16 13:00:00', 'OAK', 'MIA', -2.5, 13, 35, 0),
    	(24, 2, '2012-09-16 13:00:00', 'CLE', 'CIN', 7.5, 27, 34, 0),
    	(25, 2, '2012-09-16 13:00:00', 'HOU', 'JAX', -7.5, 27, 7, 0),
    	(26, 2, '2012-09-16 13:00:00', 'BAL', 'PHI', 2.5, 23, 24, 0),
    	(27, 2, '2012-09-16 16:05:00', 'WAS', 'STL', -3.5, 28, 31, 0),
    	(28, 2, '2012-09-16 16:05:00', 'DAL', 'SEA', -3.5, 7, 27, 0),
    	(29, 2, '2012-09-16 16:25:00', 'NYJ', 'PIT', 6.5, 10, 27, 0),
    	(30, 2, '2012-09-16 16:25:00', 'TEN', 'SD', 6.5, 10, 38, 0),
    	(31, 2, '2012-09-16 20:20:00', 'DET', 'SF', 6.5, 19, 27, 0),
    	(32, 2, '2012-09-17 20:30:00', 'DEN', 'ATL', 3.5, 21, 27, 0);
    	
    	CREATE TABLE IF NOT EXISTS `nfl_picks` (
      `userpk` smallint(6) NOT NULL,
      `gameno` smallint(6) NOT NULL,
      `weekno` tinyint(4) NOT NULL,
      `pick` char(3) NOT NULL,
      PRIMARY KEY  (`userpk`,`gameno`),
      KEY `weekno` (`weekno`,`userpk`)
    );
    INSERT INTO `nfl_picks` (`userpk`, `gameno`, `weekno`, `pick`) VALUES
    	(26, 1, 1, 'NYG'),
    	(26, 2, 1, 'NE'),
    	(26, 3, 1, 'CLE'),
    	(26, 4, 1, 'WAS'),
    	(26, 5, 1, 'MIA'),
    	(26, 6, 1, 'KC'),
    	(26, 7, 1, 'MIN'),
    	(26, 8, 1, 'BUF'),
    	(26, 9, 1, 'DET'),
    	(26, 10, 1, 'CHI'),
    	(26, 11, 1, 'CAR'),
    	(26, 12, 1, 'SEA'),
    	(26, 13, 1, 'GB'),
    	(26, 14, 1, 'DEN'),
    	(26, 15, 1, 'BAL'),
    	(26, 16, 1, 'SD'),
    	(26, 17, 2, 'GB'),
    	(26, 18, 2, 'ARI'),
    	(26, 19, 2, 'NYG'),
    	(26, 20, 2, 'BUF'),
    	(26, 21, 2, 'NO'),
    	(26, 22, 2, 'MIN'),
    	(26, 23, 2, 'OAK'),
    	(26, 24, 2, 'CIN'),
    	(26, 25, 2, 'JAX'),
    	(26, 26, 2, 'PHI'),
    	(26, 27, 2, 'WAS'),
    	(26, 28, 2, 'DAL'),
    	(26, 29, 2, 'PIT'),
    	(26, 30, 2, 'SD'),
    	(26, 31, 2, 'SF'),
    	(26, 32, 2, 'ATL'),
    	(85, 1, 1, 'DAL'),
    	(85, 2, 1, 'NE'),
    	(85, 3, 1, 'PHI'),
    	(85, 4, 1, 'WAS'),
    	(85, 5, 1, 'MIA'),
    	(85, 6, 1, 'ATL'),
    	(85, 7, 1, 'JAX'),
    	(85, 8, 1, 'BUF'),
    	(85, 9, 1, 'STL'),
    	(85, 10, 1, 'CHI'),
    	(85, 11, 1, 'CAR'),
    	(85, 12, 1, 'SEA'),
    	(85, 13, 1, 'SF'),
    	(85, 14, 1, 'PIT'),
    	(85, 15, 1, 'CIN'),
    	(85, 16, 1, 'SD'),
    	(85, 17, 2, 'CHI'),
    	(85, 18, 2, 'NE'),
    	(85, 19, 2, 'TB'),
    	(85, 20, 2, 'BUF'),
    	(85, 21, 2, 'CAR'),
    	(85, 22, 2, 'IND'),
    	(85, 23, 2, 'MIA'),
    	(85, 24, 2, 'CLE'),
    	(85, 25, 2, 'JAX'),
    	(85, 26, 2, 'BAL'),
    	(85, 27, 2, 'STL'),
    	(85, 28, 2, 'DAL'),
    	(85, 29, 2, 'NYJ'),
    	(85, 30, 2, 'SD'),
    	(85, 31, 2, 'DET'),
    	(85, 32, 2, 'ATL');
    load those tables up and have a look

    feel free to ask questions
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2013
    Posts
    6
    Rep Power
    0
    r937, I can tell how you have it set up by your code, thanks!! Definitely helps. Btw, whats your website url??
  8. #5
  9. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,235
    Rep Power
    4279
    Originally Posted by Tchpowdog
    Btw, whats your website url??
    http://rudy.ca, but the nfl pool is not linked, it's by invitation only
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo