Extension Documentation

Designing the Database Objects
Prev Chapter 8. Design Next

Designing the Database Objects

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.

Figure 8.1. Data model for IP Mapping 1.3.0

Data model for IP Mapping 1.3.0

About the Locations Table

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:

  1. The geolocation data is populated by calls to the IPInfoDB service for which an API key is required. This is a no cost item.

  2. 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.

Prev Up Next
Chapter 8. Design Home About the Reason Code Table
Go To Top

Joomla! Debug Console

Session

Profile Information

Memory Usage

Database Queries