I have a rather unique question, and I don't really know where to begin.

I'm designing a Bible Database (using mysql), the ERD is complete, and the idea is that multiple users will login to it through a web page and enter Bible verses into various chapters in the database, until eventually it will be filled up with the whole Bible.

A Php script displays a dialog that allows the user to select the Testament, Book and Chapter which they wish to enter verses for. The same php script also selects the next record which is ment to be inserted into the Verses table, based upon which Chapter, Book, and Testament the user has selected. The verse table which the user inserts into is keyed on book_id, chapter_id, and v_id, none of which are AUTO_INCREMENT.

Now my question is, if User B enters the same verse before User A has completed entering the same verse then what will happen and what are my options as too what I can make happen, with things such as table locking, or row locking?

Should I create entries for all the verses first, and then have the users fill them in? In this way the rows could be locked for the users as they add the verses.

Thank you,
leeand00

as soon as the user makes a selection of what she wants to enter, update the table and flag that selection as "taken"

then the user can provide the text later

Is this what you mean?:

1. Create the entry when the user chooses to enter it.
(I.E. Insert the record)
2. The user enters the record.
And since the php script of any other user will detect that the record has already been (inserted) it will tell the other user to enter the record after that record.
3. When the user who was editing the record submits the record, we first exclusively lock the record and then update it.

Now if this record is blank, like say the user had to run somewhere right in the middle of entering a verse, right? and the record was inserted but there is no verse text, when the user times out, we should delete all the records that he/she was editing?

no, what i meant is this:

1. user decides she wants to enter the text for book X chapter Y verse Z, so she goes to the web site, and types in or selects "book X chapter Y verse Z" (i.e. just the keys, not the text)

2. if database already shows that choice as taken, she gets message "sorry, somebody else has already claimed that", otherwise database flags that choice as "taken" with her userid beside it

3. later, user supplies text for it at her leisure

Oh okay I see what you mean.

Thanks you!

With all due respect r937 - I personally don't think that "locking a verse" is the best way to go here. Consider the following:

1. User A locks Book Joel, Chaper 1, Verse 1 - database flags that choice as taken.
2. User A get's distracted by a dog with a puffy tail - and leaves computer.
3. Three hours later User B tries to enter the same verse - and get's told "Choice already taken".

These kinds of locking mechanisms can get quite annoying:

- Extra fields required on database for "locked state"
- The extra overhead required to "lock" and "unlock" on a row-by-row basis.
- The (all too often) issue of having a row recorded as "locked" - when it shouldn't be. For example - if User A cancels the verse input - the database will unlock that selection. But what if they just close their browser / application? You will need some kind of process that runs each night to clean up stale locks.

IMO the best way to do this is with a double-checking mechanism:

1. User A selects Book Joel, Chapter 1, Verse 1. Application checks database to see if record is already entered. Give error message if already present.
2. User A types in verse and selects Submit. Application checks database to see if record is already entered. If already entered give error "Sorry, somebody has just finished adding that verse" - otherwise add verse to database.

While you will be querying the database twice for checking purposes - it's less overhead than actually updating the database whenever a user selects a verse. Also - the time it takes to type in a single verse is negligable - and the dual-entry situation won't happen often enough to warrant a locking mechanism.

This opinion is just due to the experience I've had in similar situations. If there is a disadvantage to my method - or a clear advantage of a locking method - let me know. I'd be interested to know if there's a better way to do this.

with all due respect, joel, i don't like your method at all

consider a hotel reservation system, and let's try your method

1. User A selects a room. Application checks database to see if record is already entered. Give error message if already present.
2. User A arrives at hotel and tries to check in. Application checks database to see if somebody is already in the room. If already taken give error "Sorry, somebody has just taken that room" - otherwise let user have the room.

feh

you're suggesting that a hotel reservation system has

- Extra fields required on database for "reserved room"
- The extra overhead required to "reserve" and "unreserve" on a room-by-room basis.
- The (all too often) issue of having a roomw recorded as "reserved" - when it shouldn't be.

me no like

me no like at all

no offence to you personally

I take no personal offence - I enjoy technical / design discussions such as these. I hope you don't mind if I don't agree completely with your opinion either

There's a big difference between the analogies we are making. You are talking about a hotel reservation system - which obviously needs to keep certain rooms reserved (or locked) from use. Yes, I will know 6 months in advance of December that I wish for room 10 to be free. But leeand00 is actually talking about entering bible verses into a database.

I'd be surprised if his users will be saying, I'd really like to enter Matthew Chapter 3 Verse 16 in 6 months time - could you please make sure noone enters it before me (even though it is a popular verse). I thought the problem was more that the collaborative effort of entering verses into a database would eventually lead to two people trying to enter the same verse at the same time. I didn't think we were trying to create a bible entry reservation system.

Your suggestion would cause the following:

1. User A wants to enter Matthew Chapter 3 Verse 16
2. [Database checks that verse is locked. If locked - throws error. If not locked - locks verse by placing userid on database record]

3A. User A finishes entering verse
4A. [Database saves data entered on verse, and removes locking userid from database record]

OR
3B. User A cancels entering verse
4B. [Database removes locking userid from database record]

OR
3C. User A closes application without saving / cancelling changes
4C. [Database does not know that user has finished with verse, so lock stays]

In the example above - how would you suggest we deal with 3C - a lock on a verse that is not being removed by the user? You would either need someone to clean up the lock manually (remove the userid from the verse record) or some kind of periodical process that removes locks that are not in use.

leeand00's database will be giving the users the "next" verse to enter in the system. For example - let's say that 21 verses have already been entered:

Your method

1. User A logs on - and is assigned verse 22. This verse is flagged as "taken".
2. User B logs on - and is assigned verse 23 (as verse 22 is already taken).
3. User A cancels from the application (verse 22 is not entered).
4. User B completes entering verse 23.
5. User C logs on - and is assigned verse 24 (as verse 23 has been completed).

Result - Verse 22 was never entered - and will not be assigned again (as leeand00 stated that the application would automatically assign the next verse to a new user)

My method

1. User A logs on - and is assigned verse 22.
2. User B logs on - and is assigned verse 22 (as verse 22 has not yet been finished).
3. User A cancels from the application (verse 22 is not entered).
4. User B completes entering verse 22.
5. User C logs on - and is assigned verse 23 (as verse 22 has been completed).

Result - In this case - verse 22 was correctly entered - and we do not have a missing verse from the list.

All that leeand00 is trying to prevent is two users attempting to insert the same verse. Although this is a highly unlikely event (as it takes no more than a minute to type in a verse) - we can prevent it by double-checking that the verse has not been entered. I don't feel that it is necessary to add a "userid taken" field on the table to keep track of who has been "assigned" which verse. This is a much easier solution than trying to implement a locking system on each verse of the bible.

(sorry for the long post - just trying to make sure I'm not too vague)

leave it on until another user goes to grab it, and then if the time elapsed is more than a full day, let the second user have it -- and that's just one of several strategies

i didn't understand all that verse 22, 23, 24 stuff you wrote

i thought the whole point was that they could choose whichever verses they wanted to

i gotta tell you, if i check some online application and find something is "free" and then spend time -- even a few minutes -- typing it up, and then get told "sorry, it's not free anymore" then i'm going to be p1ssed

it's all about managing user expectations, not what you might consider "unnecessary columns" and "overhead"

Exactly, it's just one of several strategies - each of which will have a disadvantage. For example - I really want to add a particular verse, so I lock it. But I don't get around to adding it until three days later - at which point it has already been released. Plus - as stated earlier - the whole locking concept creates unnecessary complication.

Not really:

While the user does get to chose the Book and Chapter - the verse is just auto-increment. So I pick Matthew Chapter 2 - I can only enter the first verse that has not been entered.

This situation will only arrise when two users select the same Book and Chapter, at the same time. I'd prefer that than having a whole Book / Chapter locked for the whole day - because a user did not "unlock" the entry.

I'll try again (I told you I'm too vague sometimes ):

1. You want to start filling in Mark, Chapter 1
2. (as per leeand00's specifications) you are given verse 1 to enter.
3. I log onto the application - and because you have locked verse 1 - I am given verse 2 to complete.
4. You quit without saving - and verse 1 was never entered.
5. The next person that selects our Book / Chapter will be given verse 3 to enter - but verse 1 will never again be available to enter.