Database Design

"Claim a slot" database design

Please support our Database Design advertiser: PostgreSQL or MySQL? Compare and contrast the two most popular open source databases

Thread Solved

•

Join Date: Oct 2009

Posts: 2

Reputation: jorisb is an unknown quantity at this point

Solved Threads: 0

jorisb

Offline

Newbie Poster

"Claim a slot" database design

Oct 18th, 2009

I'm developing a web page for a friend that allows participants of a sports event to "claim" a slot in a 24 hour tennis marathon. The marathon has room for 24 x 4 participants.

My idea was to have a table with the columns "hour" [0-23], "time" and "name", and pre-fill that table with 4 rows for each hour that have empty time and names columns.

Then, when someone fills in the form stating his name and the hour he wants to play, I'd update the table using something like

 Help with Code Tags 
 sql Syntax (Toggle Plain Text) 
UPDATE slots SET TIME = NOW(), name = "John Doe"
WHERE HOUR=14 AND name=""
BUT AT MOST 1 ROW
UPDATE slots SET time = NOW(), name = "John Doe"
WHERE hour=14 AND name=""
BUT AT MOST 1 ROW

and then I'd check PHP's mysql_affected_rows() to see if the slot was claimed successfully. Because if 5 people are trying to enroll for the same hour simultaneously, one of the attempts will have to fail.

I can't find a way to do this. So, my design is probably incorrect? Any suggestions how to tackle this? Thanks

Joris

Last edited by peter_budo; 34 Days Ago at 5:58 am. Reason: Keep It Organized - For easy readability, always wrap programming code within posts in [code] (code blocks)