Once the database requirements are defined, the next step is to turn these into a database design and an outline that describes how the user interface accepts and presents data. Given the requirements described above the two main tables can be created:
-
Location contains the IP locations
Further detail notes upon the table is provided below.
Subsidiary tables are created to provide reference data. Sample reference data is supplied on installation. The provide samples should be tailored by the site administrator to reflect actual ‘real’ life terms used by the organisation. Once obvious example is for foreign language sites to be able to tailor the codes for their own use.
-
Reasons define the ‘mapping reason’. i.e. Whether the IP address is classes as that of a site ‘visitor’ or of a ‘spammer’ or of a source of a ‘dfishield’ attack attempt, etc. The list is extensible and should be tailed for the specific site requirements.
-
Countries. This table contains a list of country names and short country codes, used in the initial release to supply the details for the module filters.
System generated primary keys are used for all tables so that all the data can be edited without executing a cascade update.
The data model designed is shown in Figure 8.1.
This table contains the IP addresses that are candidates for displaying upon a Google map. Each IP address record will contain details of the geographic location of the IP address as determined by the IP information database. This information is populated automatically when the entries are made into the table.
There are several reasons and to why it is desirable to enter IP addresses and there is an associated reason code that is used to differentiate the entries.
Table 8.1. Locations table description
Column Name |
Type |
Size |
Not Null? |
Constraints |
Description |
|---|---|---|---|---|---|
id |
Number |
n/a |
Yes |
Primary key |
A unique numeric identification for each record. Populated by an auto-sequence. |
ipaddress |
Varchar |
64 |
No |
None |
The IP address to be mapped. |
lat |
Float |
15,11 |
No |
None |
Latitude of the source of the IP address . |
lng |
Float |
15,11 |
No |
None |
Longitude of the source of the IP address. |
html5 |
Int |
n/a |
No |
None |
Specifies whether HTML5 geolocation was used to determine the location. Default of zero indicates that IP mapping was used. |
time |
Varchar |
14 |
No |
None |
Time to associate with the IP address. |
countryname |
Varchar |
255 |
No |
None |
Country name of the IP address. |
countrycode |
Varchar |
2 |
No |
None |
Country code of the IP address. |
cityname |
Varchar |
255 |
No |
None |
Name of the City where the IP address is located. |
referer |
Varchar |
255 |
No |
None |
Referer of the IP address. |
phrase |
Varchar |
255 |
No |
None |
Phrase to be displayed. |
path |
Varchar |
255 |
No |
None |
The page address upon which the location check is initiated. |
state |
Number |
. |
No |
None |
Indicates that record is visible to front end. |
agent |
Varchar |
255 |
No |
None |
IP agent. |
src |
Int |
11 |
No |
None |
Source of the ip address. |
reason_id |
Int |
2 |
Yes |
None |
'Foreign key to the ipmap_reasons table. The purpose of the entry in this table. |
logdate |
Date |
n/a |
No |
None |
Logdate of entry. |
reason |
Varchar |
255 |
255 |
None |
Reason for entry. i.e. loginfailure, adminpw, rfishield, antispam etc. [Duplicate of reason code.] |
extradata |
Mediumtext |
- |
No |
None |
Optional additional text or comments. |
ordering |
Number |
n/a |
No |
None |
Order in which records are displayed |
checked_out |
Number |
11 |
Yes |
None |
Joomla field record locking |
checked_out_time |
Date |
n/a |
Yes |
None |
Joomla field record locking |
created_on |
Date |
n/a |
Yes |
None |
Date the record was created. |
created_by |
Varchar |
255 |
Yes |
None |
The user who created the record. |
modified_on |
Date |
n/a |
Yes |
None |
The date the record was last modified. |
modified_by |
Varchar |
255 |
Yes |
None |
The user who last modified the record. |
Notes:
-
The geolocation data is populated by calls to the IPInfoDB service for which an API key is required. This is a no cost item.
-
If the IPInfoDB database returns a zero latitude and longitude this usually indicates that it could not return the true location. We have experienced problems with addresses in the 142.4.xxx.xxx and 147.255.xxx.xxx range. In this situations there is a backup check using the Geobytes database. (http://getcitydetails.geobytes.com/GetCityDetails). If you expect to exceed the services “Reasonable Free Access Limit” of 16,384 accesses per hour, (about 4.5 look-ups per second), or wish to access the service via SSL, then you may wish to purchase some Mapbytes to pay for these additional look-ups, and thereby become a VIP. We generally only use it as a backup so currently do not register as a VIP.