Schedule Table Design

[mo]

 

I have the following table defined for poker game schedules.

 

The table will be queried using keys venue ID, game type ID and day of the week from the main game table.

 

There can be more than one of the same game for the same venue on the same day just at different times or even at the same time. So, should I use an auto increment field as a key (as below) or just include start/end time as keys?

 

CREATE TABLE `game_schedule` (
  `sid` int(11) NOT NULL auto_increment,
  `venue_id` int(11) NOT NULL COMMENT 'Venue ID',
  `game_type_id` varchar(10) NOT NULL COMMENT 'Game ID',
  `day_of_week` int(1) NOT NULL COMMENT 'Day of The Week',
  `start_time` time NOT NULL COMMENT 'Start Time',
  `end_time` time NOT NULL COMMENT 'End Time',
  PRIMARY KEY  (`sid`,`venue_id`,`game_type_id`,`day_of_week`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Venue Games Schedule' AUTO_INCREMENT=1 ;

[DavidAM]

I almost always include an AUTO_INCREMENT column and make it the primary key. Integers are cheap (except in VLDB) and it makes changes easier. For instance, if you later decide to add some statistics about this game_schedule, you have a unique key to reference in your new table; without it, you have to 1) modify the table and your code to add this key; or 2) use a multi-column key in the new table.

 

Concerning your primary key definition:

PRIMARY KEY  (`sid`,`venue_id`,`game_type_id`,`day_of_week`)

Since 'sid' is auto_increment, it is unique, you should use it alone as the primary key. The rest of the fields in that definition are just wasting space. The only way an index will be used in a query is if you reference one or more fields from the start of the list. For instance:

//This will use the key
SELECT * FROM game_schedule WHERE sid = 10 and venue_id = 9;

// This will NOT use the key
SELECT * FROM game_schedule WHERE venue_id = 9;

Also, since sid is unique, you do not need to include anything else in the WHERE clause, you have already identified the record with side=10.

 

Add additional keys for fields you will use in queries. For multi-column indexes, put the columns in the order of most likely to be used in a query

INDEX (venue_id, game_type_id);
INDEX(game_type_id, day_of_week);

 

A query can only use one index per table, and it will (should) use the one that results in the fewest number of rows to scan. With the two additional indexes above:

//this query has no index to use and will do a table scan
SELECT * FROM game_schedule WHERE day_of_week = MONDAY

// This query should use an index
SELECT * FROM game_schedule WHERE day_of_week = MONDAY AND game_type_id = 4

 

These are general database design statements. It is possible that some database engines may be optimized to use part of an index without specifying the first field. But without knowing the engine you are using, and without researching the internals of that engine, these are good rules-of-thumb to go by.

[mo]

Perfect, this is exactly what I wanted to know. I will change the table accordingly. I am in the design phase and have not started coding but I want to make sure my tables are designed correctly before I start coding. I am using MySQL with PHP 5.

 

Now I just need to determine when to use ENUM and when to use foreign key tables. I like foreign key tables so that they can be used in drop down list and ENUM when I know that I will not need the values in a drop down but I do not know what is the best practice.

 

Thanks...