Designing the database

With the core components of our application identified, we can now get started with developing the database. Let's start with creating our locations table.

Locations

When developing applications that import data from an external source, you can often take advantage of the structure of the external feed to determine what your own database tables should look like. Provided with the chapter resources at protected/data/ is a file called parks.json that serves as our external data source. Since the data in this feed is consistent, let's take a look at a single item in the feed:

{
   "name" : "Cancer Survivors' Garden",
   "lat" : "41.884242",
   "long" : "-87.617404",
   "city" : "Chicago",
   "state" : "IL"
}

A single element in our data feed is composed of the name of the location, its latitude and longitude coordinates, and the city and state of the location. To make things simple, we can represent each of these attributes as a TEXT attribute in our table. Once we have added an ID column and created and updated columns, our locations table will look as follows:

ID INTEGER PRIMARY KEY
name TEXT
lat TEXT
long TEXT
city TEXT
state TEXT
created INTEGER
updated INTEGER