Mobile Line Of Business

Richard Jones (MVP)

  Home  |   Contact  |   Syndication    |   Login
  202 Posts | 0 Stories | 36 Comments | 0 Trackbacks


Welcome to the Mobile Line Of Business Blog

Tag Cloud


Post Categories


So now that we’ve established we are going to-do a glorified mail merge to build up our individual passes.   The next step is to construct a database that is going to drive the entire process.



I’ve built my data structure in SQL Server,  and I thought I would walk you through the tables I’ve built and show you how we’ll use them in the full solution.

Our initial aim, is just to produce the passes so I’m going to concentrate on that aspect.   In future posts we will look at how we send updates to passes (to use an Apple example,  boarding gate changing on a boarding pass).

Its only 4 tables,  so please bare with me as I describe their purpose.


Table – PassFoundation

The role of this table, is to hold the foundation types of passes that we are able to produce and where the mail merge template is stored on disk.  Structured as follows:

PassTypeIdentifier varchar(50) – PK
Active From datetime – PK
Stationary Folder Path varchar(255)
OutputName varchar(50)

Descriptions -

PassTypeIdentifier =
Name of path as setup in Apple developer portal (more on this later)

Active From = 1 JAN 2012

Stationary Folder Path = \Projects\PassMaker\PassMaker\Stationary\BoardingPass
Stationary path is where all the icons etc. are stored for this path. + a template.json as described in last article.

OutputName = boarding
What to call the pass when it is produced


Table – PassJob

Holds a list of jobs i.e a list of passes that need producing.  Structured as follows:

PassTypeIdentifier varchar(50) – PK
SerialNumber varchar(50) – PK
Completed Bit
Created datetime
Updated datetime

Descriptions -

PassTypeIdentifier =

Serial Number = unique serial number for this job,  i.e produce me a boarding pass serial number 1234,  it is the foreign key we use for describing this individual pass.

Completed = whether job has been processed or not.


Table – PassJobField

Stores individual fields that we want to substitute into our pass, i.e Relevance Date, or Barcode.  This is the heart of our Passbook engine.

fieldname varchar(50) = PK
SerialNumber varchar(50) – PK
PassTypeIdentifier varchar(50) – PK
Value varchar(50)
Datatype int
Sequence int
Locale varchar(2)

So this is used to store our substitutes that we can mail-merge into our passtemplate for a given pass type and a given field for a given serial number.

Table – PassJobSectionField

Finally.   For the different section of the pass we need to store the more complex structured fields, like passport number, terms and conditions etc.   This has a similar structure to the above but contains a few more formatting options.

SerialNumber varchar(50) – PK
PassTypeIdentifier varchar(50) – PK
PassSection int – PK
label varchar(50) – PK
key varchar(50)
value varchar(1000)
changeMessage varchar(50)
Locale varchar(2)
Sequence int
TextAlign int

Description -
So this would hold we have a field called ‘Passenger’, with a value of ‘Richard Jones’,  that would exist in the primary fields section of our pass.    All of this is held for a given type of pass and for a given serial number.


So next time, we’ll look how can can build a .net service for using all this data to build fully automatically our own pass and issue it out to an iOS device.

posted on Wednesday, July 25, 2012 8:54 AM