Asking for input about travel document application

[groovey]

Hi, I'm designing a travel document application.

So here’s a little description about the application:

·    A person input his/her personal data to application

·  There are 3 types of travel documents , let’s say type A, B, and C

·  A number of new travel documents will be inputted to the application as the number of free travel documents are running out of stock. (So let’s say 3000 travel documents for each document type  (A0001-A3000 for type A, B0001-B3000 for type B and C0001-C3000 for type C will be inputted to the application as the number of each travel document type are nearly out of stock))

·  Application will assign a person a travel document in a sorted way. So let’s say 2 persons (X and Y) request travel documents type A accordingly, and the last number of used travel document type A is A2500 Mr X will get A2501 and Mr Y will get A2502.

And these are the relevant tables I am using in the application:

applicants, approved_applicants and documents

CREATE TABLE `applicants` (

  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,

  `name` varchar(30) NOT NULL,

  `pob` varchar(25) DEFAULT NULL,

  `dob` date NOT NULL,

'reg_num' varchar(25) NOT NULL,

  PRIMARY KEY (`id`)

)

 

CREATE TABLE `approved_applicants` (

  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,

  `name` varchar(30) NOT NULL,

  `pob` varchar(25) DEFAULT NULL,

  `dob` date NOT NULL,

  'file_num' varchar(25) NOT NULL,

  PRIMARY KEY (`id`)

)

CREATE TABLE `documents` (

  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,

  `document_number` varchar(10) NOT NULL,

  `approved_applicant_id` int(10) DEFAULT NULL,

  `status` varchar(15) DEFAULT NULL,

  `app_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

  `created` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',

  `modified` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',

  PRIMARY KEY (`id`)

)

 

the process in the application is like this:

after a user submit an application form, his/her data will be saved to appliants table.

And then when the person appears in our office, we will confirm his data, and if the data is ok, it will be

saved to approved_applicants table.

As a person data is in approved_applicants table, we assign him/her a travel document.

The problem I want to ask is how I can track the travel documents that are already used so I can get

a free travel document in a sorted way?

 

I’ve come up with two approaches. They are:

    * Storing new travel documents in documents table.

This table is primarily used to store travel documents that are already in use. The drawbacks with this approach is the used and free travel documents will be in the same table.

    * Storing new travel documents in separate table from documents table, namely travel_document_stocks.

 

The process is as follow:

 

I input new travel documents to this table. When I assign a person a passport, I can query this table for the smallest unused number for requested travel document type. And then  I insert that new records to documents table, changing the flag for that document from 'new' to 'used' in travel_documents_stock.

 

So what I want to ask is which one is the better approach ?

Or maybe anyone can give me another solution??

Thanks

 

 

 

 

 

 

 

 

[Infinitive]

Well first of all, do you really need separate applicants and approved_applicants tables?  Why not just have a single applicants table, with a boolean field "approved" that defaults to FALSE?  That way instead of copying you just update.

 

(Your way might be justified depending on usage, e.g. most applicants are rejected and you want a smaller table for the approved ones.  I'm just tossing this out there to make sure it's what you want.)

 

To your actual question, what you need to be concerned about is "Concurrency" - two things happening at the same time.  Suppose your applicants X and Y are both applying from separate computers at the exact same time.  Serving X you check and see A2501 is the first one available.  Serving Y you check and also see that A2501 is open.  So they both try to use it.

 

That's the problem you need to worry about and avoid.  Whether you keep the same table or create a second is actually less important and I think the answer will come naturally as a result of solving this.  You may end up needing to "lock" the table until X is finished, and if you're using locks then you might prefer the second table so you're not locking used documents from people who need them.

 

One place to start looking would be here:

http://www.brainbell.com/tutors/php/php_mysql/Transactions_and_Concurrency.html

 

 

[ignace]

That's the problem you need to worry about and avoid.  Whether you keep the same table or create a second is actually less important and I think the answer will come naturally as a result of solving this.  You may end up needing to "lock" the table until X is finished, and if you're using locks then you might prefer the second table so you're not locking used documents from people who need them.

 

primary key's are by nature protected against concurrency (I think, I hope, http://www.mysqlperformanceblog.com/2006/06/05/innodb-thread-concurrency/) therefor put all document names into a separate table like you already did but remove the applicant_id in that table. Then in your main table:

 

ALTER TABLE applicant ADD document_id int(11) NOT NULL AUTO_INCREMENT

 

However this does mean that you can only tell them the document number after they have submitted.

[groovey]

Thanks for the input and sorry for the late reply..

(Your way might be justified depending on usage, e.g. most applicants are rejected and you want a smaller table for the approved ones.  I'm just tossing this out there to make sure it's what you want.)

That's true..approved_applicants table is used as.... (well as the table name says).. to store approved applicants. There should be some validations done by an operator before a record is inserted..While in applicants table.. this table can receive so many 'junk records' from careless users...

 

@ignace

Why do you think putting document_id in the applicants table is better than the other way around  ?