Setting up databases to produce game schedules...

[Jim R]

There are approximately 420 school teams in our state for basketball, and I'm producing schedules for each team.  I'm looking for recommendations on how to set up the data tables.  I'm just getting started on the structure, and I'm under no pressure for it to go live until early November.

I have a Schools data table.  Each schools has their own ID.

I have a Games data table.  Each game will have its own ID.  Each game will have a column for the home team and a column for the away team (other columns too).

For home team and away team, should I type the school name or the associated School ID?  Right now I have it set up to use the School ID.

Games Table:

 

Schools Table:

 

Is that pretty SOP?

[Barand]

You are correct in using the school ids in the games table instead of the names. You also need to extend that principle to city and conference. You should store their ids in the school table instead of repeating their names.

Why don't you store the game date (2019-11-23) instead of breaking it into 3 fields?

If it helps, their is a sample application in my sql tutorials (see link in my sig) which uses exactly this type of table.

[Jim R]

My only concern for using School ID vs. school names is making it difficult for data entry, always having to look up the school ID to input.  

Why extend it to city and conference?  Keep in mind, lots of smaller schools aren't named after their city because they're consolidations and not repeated instances.

 

 

As for the date, probably not knowing enough about the date functions.

Edited May 14, 2019 by Jim R

[Jim R]

As for the date, probably not knowing enough about the date function, thinking it would be easier when it came time to printing, since I want to print month abbreviations.  

[Barand]

15 minutes ago, Jim R said:

My only concern for using School ID vs. school names is making it difficult for data entry, always having to look up the school ID to input.  

You would provide a dropdowm menu of schoolnames built from the school table

 

16 minutes ago, Jim R said:

Why extend it to city and conference?

"Data normalization" is the fundamental principle behind relational databases (Google it)

[Jim R]

1 minute ago, Barand said:

You would provide a dropdowm menu of schoolnames built from the school table

 

We'll be typing the games directly into the database.  

[Barand]

30 minutes ago, Jim R said:

As for the date, probably not knowing enough about the date function, thinking it would be easier when it came time to printing, since I want to print month abbreviations.  

Store dates as DATE type for maximum functionality (format yyyy-mm-dd). You can output them as you like EG

mysql> SELECT test_date
    ->      , DATE_FORMAT(test_date, '%d-%m-%y') as example1
    ->      , DATE_FORMAT(test_date, '%d-%b-%y') as example2
    ->      , DATE_FORMAT(test_date, '%M %D') as example3
    -> FROM test_b;
+------------+----------+-----------+---------------+
| test_date  | example1 | example2  | example3      |
+------------+----------+-----------+---------------+
| 2019-05-14 | 14-05-19 | 14-May-19 | May 14th      |
| 2019-12-25 | 25-12-19 | 25-Dec-19 | December 25th |
+------------+----------+-----------+---------------+

 

27 minutes ago, Jim R said:

We'll be typing the games directly into the database.  

Not your best idea. I had client who did that with invoices from suppliers. They would type in the supplier names, invoice numbers amounts etc. Some of the suppliers had half-a-dozen different spellings of their names. It made reporting by supplier a nightmare.

Database tables are not spreadsheets.

[Jim R]

1 minute ago, Barand said:

Not your best idea. I had client who did that with invoices from suppliers. They would type in the supplier names, invoice numbers amounts etc. Some of the suppliers had half-a-dozen different spellings of their names. It made reporting by supplier a nightmare.

Database tables are not spreadsheets.

I'm with you on that, and the list with ID#'s can be viewable while entering game data.  Either way, we're going to have to scroll to match data.  I suppose I could set up a form, which would then check to duplicates. 

 

I use Sequel Pro to view and edit my databases, and it's been great over the years.  It has a Relations tab in table view.  However, it won't allow me to create Relations for the tables.  Something to do with the database engine.  It would be nice if there was an application that allowed me to set up a Relation between tables, then as school names are type, creates auto-complete options.  

 

 

[Jim R]

Upon further review, I can change the storage engine for each table in Sequel Pro.  I can now set up Relations between tables.  (Not sure what that means yet.)  

Do you only columns with integers or numeric values show up as Relationship options?

 

UPDATE:  Can not add Relation.  Can not add foreign key constraint. 

Edited May 14, 2019 by Jim R

[Barand]

I've never heard of Sequel Pro so can't help you there. As you are posting in a MySql forum you might consider using MySql, perhaps.

[Jim R]

4 minutes ago, Barand said:

I've never heard of Sequel Pro so can't help you there. As you are posting in a MySql forum you might consider using MySql, perhaps.

That's just an application.  I'm using a MySql database.

Edited May 14, 2019 by Jim R

[Barand]

2 hours ago, Jim R said:

Do you only columns with integers or numeric values show up as Relationship options?

Recommended. Numeric keys are more efficient. Names and other data items should only occur once in a database. IDs are the only items that should be duplicated across tables to form the relationships.

This is what I think you will need (tables and relationships)

What is "sectional"?

[Barand]

This is the SQL to create those tables with relationships as illustrated

CREATE TABLE `city` (
  `city_id` int(11) NOT NULL AUTO_INCREMENT,
  `cityname` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`city_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE `conference` (
  `conf_id` int(11) NOT NULL AUTO_INCREMENT,
  `confname` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`conf_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE `school` (
  `school_id` int(11) NOT NULL AUTO_INCREMENT,
  `schoolname` varchar(45) DEFAULT NULL,
  `coachfirst` varchar(45) DEFAULT NULL,
  `coachlast` varchar(45) DEFAULT NULL,
  `sectional` int(11) DEFAULT NULL,
  `city_id` int(11) DEFAULT NULL,
  `conf_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`school_id`),
  KEY `fk_school_city_idx` (`city_id`),
  KEY `fk_school_conference1_idx` (`conf_id`),
  CONSTRAINT `fk_school_city` FOREIGN KEY (`city_id`) REFERENCES `city` (`city_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_school_conference1` FOREIGN KEY (`conf_id`) REFERENCES `conference` (`conf_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE `game` (
  `game_id` int(11) NOT NULL AUTO_INCREMENT,
  `gamedate` date DEFAULT NULL,
  `home_id` int(11) DEFAULT NULL,
  `away_id` int(11) DEFAULT NULL,
  `home_score` int(11) DEFAULT NULL,
  `away_score` int(11) DEFAULT NULL,
  PRIMARY KEY (`game_id`),
  KEY `fk_game_school1_idx` (`home_id`),
  KEY `fk_game_school2_idx` (`away_id`),
  CONSTRAINT `fk_game_school1` FOREIGN KEY (`home_id`) REFERENCES `school` (`school_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_game_school2` FOREIGN KEY (`away_id`) REFERENCES `school` (`school_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

 

Edited May 15, 2019 by Barand

[Jim R]

15 hours ago, Barand said:

Recommended. Numeric keys are more efficient. Names and other data items should only occur once in a database. IDs are the only items that should be duplicated across tables to form the relationships.

This is what I think you will need (tables and relationships)

What is "sectional"?

 

The Sectional is the first round of our state tournament, so season preview information will be grouped by sectionals.  I won't be duplicating any data.  I may not even separate the city in the final version of this.  

 

My last decision will likely be how to handle the coach part because I want to compile their yearly records going forward and *might* even back fill it as time goes on.

 

I'm assuming I should not have the coach as part of the school part.  Right?  I should have a coaches table, then designate which team they coach.  However, would I be able to automate the recording of the record from season to season, even as the coaches change teams?  

 

[Barand]

Correct - there should be a separate coach table. To keep track of who coached when there would be a school_coach table defining when the coach was with the team

Current coaches would have a date value of 9999-12-31 in the coached_until column. This would be updated when the coach leaves.

Remove the coach columns from the school table.

Edited May 15, 2019 by Barand

[Jim R]

3 hours ago, Barand said:

Correct - there should be a separate coach table. To keep track of who coached when there would be a school_coach table defining when the coach was with the team

Current coaches would have a date value of 9999-12-31 in the coached_until column. This would be updated when the coach leaves.

Remove the coach columns from the school table.

 

 

After each season, as a coach changes jobs, I would add an instance/row to the coaching_record table with his new school of record? 

[Barand]

You wouldn't need to. The school_coach table gives the history of where each coach was and when.

+-----------+------------+-----------+---------+---------+                          +----------+-------+-------+
| school_id | schoolname | sectional | city_id | conf_id |                          | coach_id | fname | lname |
+-----------+------------+-----------+---------+---------+                          +----------+-------+-------+
|         1 | School 1   |      NULL |    NULL |    NULL |                          |        1 | Coach | One   |
|         2 | School 2   |      NULL |    NULL |    NULL |                          |        2 | Coach | Two   |
+-----------+------------+-----------+---------+---------+                          +----------+-------+-------+


                            +-----------------+-----------+----------+--------------+---------------+
                            | school_coach_id | school_id | coach_id | coached_from | coached_until |
                            +-----------------+-----------+----------+--------------+---------------+
                            |               9 |         1 |        1 | 2017-09-01   | 2018-08-31    |
                            |              10 |         2 |        2 | 2017-09-01   | 2018-08-31    |
                            |              11 |         1 |        2 | 2018-09-01   | 9999-12-31    |
                            |              12 |         2 |        1 | 2018-09-01   | 9999-12-31    |
                            +-----------------+-----------+----------+--------------+---------------+

Historical - who coached where on 01-Jan-2018?

SELECT schoolname
     , CONCAT(fname, ' ', lname) as coach
FROM school s
     JOIN school_coach sc 
        ON s.school_id = sc.school_id
           AND '2018-01-01' BETWEEN sc.coached_from AND sc.coached_until
     JOIN coach c USING (coach_id);      
+------------+-----------+
| schoolname | coach     |
+------------+-----------+
| School 1   | Coach One |
| School 2   | Coach Two |
+------------+-----------+

Who is currently coaching where?

SELECT schoolname
     , CONCAT(fname, ' ', lname) as coach
FROM school s
     JOIN school_coach sc 
        ON s.school_id = sc.school_id
           AND CURDATE() BETWEEN sc.coached_from AND sc.coached_until
     JOIN coach c USING (coach_id);  
+------------+-----------+
| schoolname | coach     |
+------------+-----------+
| School 1   | Coach Two |
| School 2   | Coach One |
+------------+-----------+

 

[Jim R]

But each new school is a new instance, and I would want to calculate his overall record and record at the current school.  (I realize this will take some serious back filling eventually.)

 

So if Coach A coached at School A for ten years, (to me) that would be one row, then as he changed jobs, I'd have to have Coach A at School B.  

 

 

[Barand]

9 hours ago, Jim R said:

and I would want to calculate his overall record

Define "calculate"

[Barand]

As an aside from coaching, you will need to add to your game fixtures table to indicate which games are league games (points contributing to their league position at the end of the season) and which are tournament games. Perhaps a "tournament_id" which would be 0  for league games.

For tournament games you may also want ot indicate which round of the tournament. This would tell you, for example, if a team never got past the first round or if they reach the semis or finals on N occasions.

[Jim R]

8 hours ago, Barand said:

Define "calculate"

Automatically figure who wins or loses based on scores input.  Is that possible?  Could I create a trigger for that, or would it be best to have my score input person just mark who wins and loses?

 

7 hours ago, Barand said:

As an aside from coaching, you will need to add to your game fixtures table to indicate which games are league games (points contributing to their league position at the end of the season) and which are tournament games. Perhaps a "tournament_id" which would be 0  for league games.

For tournament games you may also want ot indicate which round of the tournament. This would tell you, for example, if a team never got past the first round or if they reach the semis or finals on N occasions.

Yes, I have the games table established (not specifically in its final form).  League games here are just games, (more for bragging rights) but I will have a column to note those games (binary).  They don't figure in tournament play in Indiana.  I will definitely have a way to determine if it's a tournament game and which round (integer 0-4).  

[Barand]

Using my data model you can get results like

+---------+--------+------+-------+------+-------+--------+
| School  | Played | Won  | Drawn | Lost | PDiff | Points |
+---------+--------+------+-------+------+-------+--------+          Points awarded
| Cowdrey |      6 |    4 |     2 |    0 |     8 |     14 |             Win  = 3
| Laker   |      6 |    3 |     2 |    1 |     6 |     11 |             Draw = 1
| Jardine |      6 |    1 |     2 |    3 |    -6 |      5 |             Lose = 0
| Grace   |      6 |    0 |     2 |    4 |    -8 |      2 |
+---------+--------+------+-------+------+-------+--------+

This can be  for schools or coaches, either for the current season or going back as far as your data allows.

[Jim R]

Thoughts on updating it automatically as each score is input?  

$won = $won+1

$loss = $loss+1

The model I'm basing a lot of this on updates as scores are input.  

[Barand]

1 minute ago, Jim R said:

Thoughts on updating it automatically as each score is input?  

Don't. Just enter and store the scores.

Databases should not store derived data. Get the total wins and losses when you need them with a query.

[gizmola]

Hey Jim R,

I just want to say that I'm pleased to see you letting go of some of your preconceived notions.

There are few people with both the expertise and patience Barand brings to these forums.  

In my career I've designed and engineered systems for companies like Paramount Pictures, Industrial Light & Magic, Blizzard Entertainment, Sierra Online and many more I won't elaborate.  Some of the systems have had millions of users and 100's of millions of rows in individual tables.  Please take it from me that you're getting amazing advice from a professional who is an authority on anything having to do with relational database design, SQL or web systems based on a relational database.