CLOSED Philadelphia Tobacco Retailer Database


 Statement of Work

City of Philadelphia

Department of Public Health

OPEN: 10/12/2016   CLOSED 10/20/2016


Due to a Typo in the Scope of Work section, the Timeline for this BID has been extended 3 days.

Please note that we are looking primarily for a quote to (1) Convert our existing MS access to the Web, (2) Add additional functionality, and (3) provide the ability to interface with external agencies via Forms, APIs or other data exchange methods.


The Philadelphia Tobacco Retailers Database (PTRD) is currently hosted by the Division of Environmental Health Services as a SQL database using MS Access for front end management. The Division of Chronic Disease Prevention maintains the data and runs analyses. The PTRD stores data on the status of tobacco permits, compliance with tobacco sales laws, and tickets for sales violations. The database is small (<10,000 records).


Convert the current MS Access Frontend PTRD Database to a Web Application using a SQL Server Backend. Add additional functionality to the new Web Application. The City is looking for proposals that offer a cloud-based solution using standard BI tools in which the city can utilize for suture development and enhancements. The proposal can offer a hosted or on-premise solution.


REVISED 10/15/2016: Scope of Work Description was the wrong description, this Bid will now be open until 10/20/2016.

 Functionality Phase 1 – Porting of the Current MS Access Database to the Web

  1. Establishment Table: records on establishments that sell tobacco including permit number, address, store type, and other business owner and store information. A unique establishment ID is assigned and is linked to the business owner. Note: data entered manually by EHS using a web form when the tobacco permit is issued
  2. Compliance Check Table: records on compliance checks and their outcomes as recorded by the contractor who does the checks (Health Promotion Council). Linked using establishment ID.
    1. The list of compliance checks to be done are generated by pulling a subset of retailers in the Establishment Table and emailing an excel file to be uploaded to the contractor’s database
    2. After the checks are completed, data on the results of the checks are uploaded from an excel file emailed back by the contractor
  3. Ticket Table: records on tickets that were issued as a result of failed compliance checks. Data linked with a unique ticket number associated with the establishment (which may receive multiple tickets)
    1. Data manually entered by EHS after automatic emails are generated from any failed compliance check
    2. Data from the Office of Administrative Review on ticket outcomes is also manually merged to this table

 Functionality Phase 2 – Enhanced Features to the new Web Application

  1. Integrate additional data sources
    1. Food and Drug Administration publically available data on tobacco sales compliance available here:
    2. Data on state issued tobacco permits
  2. Access and data editing capabilities in the field via tablets
  3. Develop online tobacco permit application system (currently done with paper), which is linked to the database and can be integrated with the existing online permit renewal system or a reworked online permit renewal system.
    1. Would need to reference existing and additional data sources to determine eligibility criteria
  4. Integrate data from an outside contractor (for the Compliance Check Table)
  5. Build in additional checks on data quality and management
  6. Better automate many data updating tasks and common reporting queries
  7. Be able to interface or download data for use with popular statistical analysis programs including R, SPSS, and GIS
  8. Incorporate location-based decision making factors into the permit application system (eg, to flag any retailers that are within 500 ft of a school)


  1. Develop Detailed requirements analysis including reporting requirements
  2. Development of a Web application for phases 1 and 2 according to specs.
  3. Enhance Website with security features to protect the privacy of data as well as implement administrative and user level security functionality
  4. Perform unit and system testing on the Complete Web Application
  5. Create System Documentation
  6. Create User Guide and Training Materials


  1. Project Kick-off, project start
  • Detailed Requirements Document
  • Detailed Report Layouts
  1. Within 10 days of the start of the project
  • Test Ready Application for Phase 1
  1. Within 15 days of the start of the project
  • Production ready application for Phase 1 (including reports)
  • Test ready application for Phase 2
  1. Within 20 days of the start of the project
  • Production ready application for Phase 2
  • Online availability of all system reports for review
  1. Within 30 days of the start of the project
  • Completed Production Ready Application Phases 1 and 2
  • Completed System Reports
  1. Within 35 days of the start of the project
  • System documentation including architectural diagram and data flow diagrams
  • User guide/training document


Your proposal should include a short description of your qualifications, experience with similar projects, an overview of your firm or organization, and a brief description of your solution. You may include high level architectural diagrams of the proposed solution and URLs for any online examples you may have. With regards to pricing we are not looking for a flat fee for this project but rather a detailed breakdown which will reflect an hourly rate and the number of hours associated with each task to be performed as well as other costs related to subscriptions, licensing or cloud services if applicable. Please email all documents to and . Your submissions must be received on or before 10/20/2016. Late submissions will not be considered. Please limit the size of the proposal document to a maximum of 12 pages.


Please post any questions as a comment below this solicitation on so that additional information provided in any Q&A will be available for all potential submitters to review. Any questions sent via email will be directed back to the comment section below the solicitation on


OPEN: 10/12/2016   CLOSED 10/20/2016


8 comments on “CLOSED Philadelphia Tobacco Retailer Database
  1. Modern Technique says:

    In order to provide an accurate quote, aside from administration how many fields are going to be required? Do you intend these fields to be dynamically required/displayed based on user input? This is assuming there is more information than Functionality Phase 1.1.

    Functionality Phase 1.2.1 – What triggers this email? Is this a click event, nightly job, or timed?

    Functionality Phase 1.2.2/1.3.1 – The wording here leads me to believe the system is waiting for a response email however section 1.3.1 leads me to believe this is a person on the receiving end to upload it into this system or possibly the contractor themselves?

    Functionality Phase 2.2 – Assuming this means modern web-app, ignoring offline capabilities? The concern being the statement “in the field via tablets” and the also posted in Mobile Apps, not knowing if the tablet is tethered or utilizing a phone as a hotspot.

    Functionality Phase 2.7 – Would a web service (listener) be sufficient to meet this need or will this be entered data?

    Functionality Phase 2.8 – Is there currently a contract out on a service to provide the longitude and latitude of the input addresses? Is the information for all schools located in a central database? I assume the schools are accessible within the ADABAS and externally accessible?

    In terms of “taxpayer should be able to pay the tax monthly”, is this limited to storing billing information or will this involve a processing service? If processing, is this automated or simply allowing the user to make one off payment? Or Both? Does the city currently have a contract in place to address this?

    What is your intention in terms of “cloud based”? Do you mean a pay as you use system, redundancy assurance, or exists within a cloud system? Along that note, what is the up time regulations for this department? All government contracts thus far have been 99.999% (not 99.99%)? The only prior government contracting has been health care or military which is all 99.999%. The difference clearly cost, I would imagine this to be only 99.99%, but I would like to be certain, again this comes down to the regs.

    As the middleware is hosted by the city, will the connection service simply be an HTTPS connection or is there additional security or network requirements?

    • rondaniels says:

      There was a typo in the Scope of work where a paragraph from a previous posting was previously added.
      We have since removed this typo and added 3 days to the bid request.

      I am hoping this should clarify most of your questions but if not please post again as I will be reviewing daily up until the submission deadline

  2. jplatt says:

    1. We’ve noticed that the objectives and scope of work seem to contradict each other in relation to database requirements. Can we get clarification?

    2. Would an extension be possible as response time to the RFP is only 3 business days?

    • rondaniels says:

      Yes, we have extended the bid for 3 days (now due on 10/20) due to a typo in our scope of work.
      We are looking for a solution that will convert our current MS Access database to the web
      then add additional functionality, then allow us to interface with other agencies via forms, APIs or other.

  3. aries says:


    A couple of questions.

    The scope of work states: “Design a web application that will enable the business owners to register their sweetened beverage business with the City.” Can you provide and updated scope of work and the associated technologies?

    Functionality Phase 2 – Enhanced Feature 8 – “Incorporate location-based decision making factors into the permit application system”. Does the City already have a GIS tool that we would access, or do we have to provide the GIS tool?

    Thank you!

    • rondaniels says:

      The Scope of work description was a typo from an earlier post that was submitted by the City.
      It has since been corrected and the bid timeline has been extended 3 days because of this error.

      With regards to your GIS question, we are looking for you to provide a solution. The City has the GIS data for each of the schools within our city limits but will need a mechanism by which when an application is submitted we can plot the distance between the establishment address and the nearest school.

  4. SmartIMS says:

    Hi Ron,
    We have a quick question:
    Are the requirements frozen with respect to Web pages UI Design, documented requirement tasks and each of its acceptance criteria?

    • rondaniels says:

      I would not say frozen however I do not expect there to be many web pages (5 to 10) for this project. A lot of the work will be around the database design and the ability to link tables together along with interact with external tables (ie through import/export or API)

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.