• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

B&S: Primary Key Considerations??

 
Jack Gold
Ranch Hand
Posts: 85
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I estimate my project is 70% done. After getting the framework in place and each class unit tested, I cobbled toggether end-to-end functionality, but still have some business rules and remote access to implement.

However, I have come to a point where I question my original design strategy regarding unique identification of records. It seemed like the easy solution was to use "recNo" (physical position in file) as the primary key. The problem arises when dealing with the scenario where another client deletes a record and then adds a new record (because of the requirement to reuse space). The original client does not know that the record in question has changed, so attempts to access it through its record number will be invalid.

Obviously when working with any DB, a valid primary key is required (and the recNo probably fails one or more of the criteria for a valid PK-- need to dig out my database textbook. . .) Anyway, I realize the name/location pair can be used as a composite primary key.

Before I embark on making the changes to replace recNo with a composite primary key, is there any valid way to salvage the use of recNo? I read a ew people who had used recNo as a PK, but I cant see a way around the problem I mentioned.

If ClientA deletes then adds a new record, ClientB will have no way of knowing that recordX no longer references the original record.

Opinions?
 
John Smith
Ranch Hand
Posts: 2937
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
If ClientA deletes then adds a new record, ClientB will have no way of knowing that recordX no longer references the original record.

Deleting a record is a form of record modification, and both client A and client B should follow the "read-lock-checkIfReplacedOrDeleted-modify-unlock" sequence. This is, of course, because in between read and lock, some other thread might have modified/deleted that record, as you pointed out. This sequence will guarantee that you are safe, no matter how much you tear apart the CPU time between multiple threads/clients.

In my case, the delete() method of Data would simply mark the record as "DELETED" and the add() method would increment the record count and write the new record at the end of the file. That is, I didn't have the "save the damn space" requirement in my assignment. So, for me, the "checkIfReplacedOrDeleted" action was reduced to simply checking if the record is still "LIVE". In your case, where the new record could be written in place of the deleted one, you could include another check to see if the primary key is still the same. Not sure what you would use as a primary key, but if you have a "DatabaseException("Attempt to add a duplicate key")" piece of code in you add() method, that's a good hint.

It's been a few decades since I took the exam, and at that ole' good time the Data class was provided as is, fully written, except the lock/unlock methods. Are you poor folks now required to write it from scratch?
 
Jack Gold
Ranch Hand
Posts: 85
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Yes. We get an interface that Data must implement. We also get a data file.

Regarding the check to see if the record has changed, I think that would work. I would need another method that takes the name and location (which together are unique) along with the record number. The method would check to see if name and location are still the same for a given record number, but this would need to be inserted into almost all operations. Thanks.
 
John Smith
Ranch Hand
Posts: 2937
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Regarding the check to see if the record has changed, I think that would work.

More precisely, the check would be to see if the record was deleted or replaced by another record. Simple modification is OK, because even if some other thread "booked" that record before you, you are still inforcing your business rules after you locked that record (such as checking the remaining count of the field or whatever your rules are).
 
Lara McCarver
Ranch Hand
Posts: 118
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I had not thought about this concern (that the Client app won't know about modified data in the database).

This gives extra incentive to implement a layer on top of Data. In this layer, the Update() and Delete() methods can take additional arguments, like the key. Or, they could just take the whole original record, and then call some private method within the class to extract the key from the record.

So really, you are just adding an Adaptor on top of your current Data class... it shoudn't be too bad.
 
Jack Gold
Ranch Hand
Posts: 85
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by Lara McCarver:
e key from the record.

So really, you are just adding an Adaptor on top of your current Data class... it shoudn't be too bad.


Yes, this would be in the DataAdapter class.

As far as being easy, retrofitting all the methods with name/location PK could be time consuming and involve alot of retesting. From where I am now, one method to check if the PK of the record has been modified is the easiest, but I'm sure there is a more elegant solution.
 
John Smith
Ranch Hand
Posts: 2937
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
This gives extra incentive to implement a layer on top of Data. In this layer, the Update() and Delete() methods can take additional arguments, like the key.

That's one solution. The alternative solution is to let the caller worry about it. That is, leave the Data class along with no knowledge of how it should handle the co-modification, but load the caller with the responsibility to check if the record was deleted/replaced in the middle of the modification request. The only place where you would be really doing it is your book() method (I assume that your requrements don't ask you to actually have a clickable "delete record", but only the "book" and "search" button). Since the search can be a "dirty" read, that leaves only "book" method to feel paranoid about.

I guess I am trying to push you cowboys and girls not to add impurities to the Data class (or even to its adapter). Take the concept of separation of responsibilites and narrow class scope straight to your heart. Keep the cattle in the barn, have the guesthouse for the city folks (so that you don't have to share you bed with them), and don't ask the sheriff to do slaughter your chickens.
 
Jack Gold
Ranch Hand
Posts: 85
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
John,
Thanks for your advice, but I belive it might be a simplification. First off, even though the requirements have not explicitly stated it, I have clickable add/delete buttons. I believe these requirements were implied, otherwise there is no way to test much of the other functionality specified in DBAccess interface.

Secondly, this is not as simple as the client doing a lone call to checkModifyDeleted(PK) before booking(modifying) a record. Technically, for this to be failsafe, it needs to be atomic.

Client A could do the check and find out the record has not changed, then ClientB could change the record before ClientA has a chance to finish his booking (or deletion) operation.

So this means that the checkModifyDeleted() needs to be performed on the server atomically with the other parts of the operation. It also means that the delete, book, and unbook methods must provide a primary key to the server.

Calls to checkModifyDeleted() would need to go in the data adapter because it handles the locking.

So the call stack might be:

User Clicks Book->
Event Handler for Book ->
GUIController.book(name, location) ->
DataAdapter.bookContractor(name,location) ->
* lock
-> Data.checkModifyDeleted(name, location)
-> Data.updateRecord(recNo)
* unlock

Something similar would need to be done for delete and unbook.
[ June 08, 2005: Message edited by: Jack Gold ]
 
Lara McCarver
Ranch Hand
Posts: 118
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
That's one solution. The alternative solution is to let the caller worry about it. That is, leave the Data class along with no knowledge of how it should handle the co-modification, but load the caller with the responsibility to check if the record was deleted/replaced in the middle of the modification request.


What is driving my decision so far is the desire to hide lock() and unlock() from any interface that the client sees. I think that in a real world situation, programmers who work on client apps will be specializing in GUI code, while programmer who work on server apps will be specializing in threading, RMI and databases. I think that lock() and unlock() are technical details related to server-side things, so that's where I would prefer to keep this logic. I realize that this gets a little weird with this app because there is only one app and it is used for both the client and the server (based on my requirements).

So that is why, when I see an operation which is going to require a sequence like this:



I start thinking that I should put this sequence inside a method which resides on the server and is callable by the GUI.
 
John Smith
Ranch Hand
Posts: 2937
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by Jack Gold:

Secondly, this is not as simple as the client doing a lone call to checkModifyDeleted(PK) before booking(modifying) a record. Technically, for this to be failsafe, it needs to be atomic.

Client A could do the check and find out the record has not changed, then ClientB could change the record before ClientA has a chance to finish his booking (or deletion) operation.



Ah, but this would never happen if both clients A and B are executing the "read-lock-checkIfReplacedOrDeleted-modify-unlock" sequence in their respective threads. Notice that the check is after the lock, and therefore one thread will wait() until notified from the unlock() of the other thread.
 
John Smith
Ranch Hand
Posts: 2937
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I start thinking that I should put this sequence inside a method which resides on the server and is callable by the GUI.

Just a bit of refinement: the caller should be the controller (if you are implementing an MVC) or perhaps some other class. The GUI should only know how to render the graphic widgets and how to "motion" (indicate the user's intent) to the business layer.
[ June 08, 2005: Message edited by: John Smith ]
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic