How to avoid duplicate registrations in MySQL

Question

I wonder if it is possible to restrain users to insert duplicate registration records. For example some team is registered from 5.1.2009 - 31.12.2009. Then someone registers the same team for 5.2.2009 - 31.12.2009. Usually the end_date is not an issue, but start_date should not be between existing records start and end date

CREATE TABLE IF NOT EXISTS `ejl_team_registration` (
  `id` int(11) NOT NULL auto_increment,
  `team_id` int(11) NOT NULL,
  `league_id` smallint(6) NOT NULL,
  `start_date` date NOT NULL,
  `end_date` date NOT NULL,
  PRIMARY KEY  (`team_id`,`league_id`,`start_date`),
  UNIQUE KEY `id` (`id`)
);

Answer 1

I would check it in the code df the program, not the database.

Answer 2

Before doing your INSERT, do a SELECT to check.

SELECT COUNT(*) FROM `ejl_team_registration`
WHERE `team_id` = [[myTeamId]] AND `league_id` = [[myLeagueId]]
    AND `start_date` <= NOW()
    AND `end_date` >= NOW()

If that returns more than 0, then don't insert.

Answer 3

If you want to do this in database, you can probably use pre-insert trigger that will fail if there are any conflicting records.

Answer 4

This is a classic problem of time overlapping. Say you want to register a certain team for the period of A (start_date) until B (end_date).

This should NOT be allowed in next cases:

1. the same team is already registered, so that the registered period is completely inside the A-B period (start_date >= A and end_date <= B)
2. the same team is already registered at point A (start_date <= A and end_date >= A)
3. the same team is already registered at point B (start_date <= B and end_date >= B)

In those cases, registering would cause time overlap. In any other it would not, so you're free to register.

In sql, the check would be:

select count(*) from ejl_team_registration
where (team_id=123 and league_id=45)
and ((start_date>=A and end_date<=B)
or (start_date<=A and end_date>=A)
or (start_date<=B and end_date>=B)
);

... with of course real values for the team_id, league_id, A and B.

If the query returns anything else than 0, the team is already registered and registering again would cause time overlap.

To demonstrate this, let's populate the table:

insert into ejl_team_registration (id, team_id, league_id, start_date, end_date)
values (1, 123, 45, '2007-01-01', '2007-12-31')
, (2, 123, 45, '2008-01-01', '2008-12-31')
, (3, 123, 45, '20010-01-01', '2010-12-31');

Let's check if we could register team 123 in leage 45 between '2009-02-03' and '2009-12-31':

select count(*) from ejl_team_registration
where (team_id=123 and league_id=45)
and ((start_date<='2009-02-03' and end_date>='2009-12-31')
or (start_date<='2009-03-31' and end_date>='2009-03-02')
or (start_date<='2009-12-31' and end_date>='2009-12-31')
);

The result is 0, so we can register freely. Registering between e.g. '2009-02-03' and '2011-12-31' would not be possible. I'll leave checking other values for you as a practice.

PS: You mentioned the end date is usually not an issue. As a matter of fact it is, since inserting an entry with invalid end date would cause overlapping as well.