Neil,
> Thanks Josh. I agree with you that it would be preferable to have a better
> design - But since all 'periods' are constrained based on each other, I can
> see no other way than locking all tables to prevent a race hazard. Any
> ideas you have an any improvment would be gratefully received - I'm quite
> new to database design!
You've chosen a tough problem to start with, then. Calendaring in SQL is a
pain, mostly due to a bunch of bad decisions made by the SQL committee in the
early 90's.
After some thought, there isn't an *easy* way to avoid locking the whole
table. There are some elaborate workarounds, but those are only worth
pursuing if the locking is seriously choking your application.
--
Josh Berkus
Aglio Database Solutions
San Francisco