Numbering and Database Products

Return to Telecom
Introduction Page


Magenta UK Numbering Database - Numbering Membership

The Magenta UK Numbering Database is designed for those that need to fully integrate telephone numbering, tariff band and locality information into their own systems. The original raw database is available as a set of comma separated variable (CSV) text files that may be importing into custom databases. The database comprises the following tables (approximate number of records bracketed, it changes weekly), samples of which can be provided upon request:

  • AllCodes.csv - The main lookup table (292,298)
  • AreaCodes.csv - Area names and codes (834)
  • Bands.csv - Tariff bands, descriptions and codes (1,082)
  • Countries.csv - Full international information (720)
  • DescCodes.csv - Various look-up descriptions (40)
  • Distance.csv - Shows local code pairs (21,396)
  • Localites.csv - Locality, post town, post code and area code (5,542)
  • Operators.csv - Operator contact info (841)

The numbering database is a snapshot of current numbers and tariff bands, usually updated weekly, as new source data from BT and Ofcom is released.

Cost of the Magenta UK Numbering Database is 1,000 for the first year (which include full site membership), and 300 for subsequent years. Existing site members will be credited with their current fee and so only pay the difference. This price licenses the database to be used in one company or organisation only, and does not allow redistribution with applications. Please contact Magenta Systems Ltd for details on redistribution licensing.

The Magenta UK Numbering Database is distributed in a ZIP file, and may be downloaded from the password protected Numbering section of this web site. It is updated weekly, or less often if there is no new numbering or tariff information from Ofcom or BT.



Magenta SQL Telecom Database - Database Membership

The Magenta SQL Telecom Database offers a Microsoft SQL Server 2008 R2/2012 database with both numbering and tariff information, exactly the same database that drives this web site. Unlike Numbering Membership, Database Membership includes all our tariff information which may be used to cost telephone calls for billing or competitive purposes using about 500 different tariffs. A single Microsoft SQL Server 2008 R2/2012 database file is supplied that may be quickly restored to other SQL servers, rather than needing to import lots of CSV files.

The database includes various stored procedures to access the 29 tables with about 800,000 rows of numbering, locality and tariff data. For use with other SQL databases, CSV files are also be available. The SQL database is historic, containing old and current numbering and tariff information allowing telephone calls to be costed as at a specified date. 

Please note the tariffs in the database are currently taken from the tariff spreadsheet and only include costs to about 40 countries.  Systems are currently being developed to automate the import of tariffs from spreadsheets, CSV and XML files, and web pages (similarly to how numbering has been done for 10 years) so costs for all charge bands will finally be available.

Cost of the Magenta SQL Telecom Database is 2,000 for the first year (which include full site membership), and 1,000 per year subsequently. Existing site members will be credited with their current fee and so only pay the difference. This price licenses the database to be used in one company or organisation only, and does not allow redistribution with applications. Please contact Magenta Systems Ltd for details on redistribution licensing.

The main database is named magtelecom and is built with Microsoft SQL Server 2008 R2, but is fully compatible with SQL Server 2012.   There are five main tables, 12 child tables, one work table and nine small type validation tables.  

Table Name

Expected Rows

Purpose

Description

area codes

5,000

Child

Area names and codes

band costs

70,000 to 500,000

Child

All calling costs with tariff, charge band, day type, time band, start time, cost per minute, set-up and tariff name and optional dial through access number, includes historic data

call costs

400,000 to 600,000

Work

Sample call costs for current tariffs for various call durations, designed for sorted selections, work created from band costs, no historic data

call lengths

7

Child

Sample call lengths for call costs 

call types

4

Child

Sample call types at different times of the week  for call costs

charge bands

1,200

Main

Tariff bands, descriptions and codes

code types

9

Types

Main code category

countries

800

Main

Full international information

cps codes

150

Child

Ofcom carrier pre selection codes CPS

cupid codes

600

Child

Ofcom communication provider identity codes CUPID

day types

11

Types

Days of the week for band costs

discounts

16

Types

Discount categories used by BT

distances

24,000

Child

Local code pairs of local call charging based on distance

free call periods

6

Types

Tariff package free call periods

localities

12,000

Child

Locality, post town, post code and area code

mnc codes

50

Child

Ofcom mobile network codes MNC

numbers

320,000 to 500,000

Main

All dialling codes with tariff band, operator and locality

op types

10

Types

Operator type, whether information is published 

operators

5,000 to 10,000

Main

Operator contact info

rentals

100 to 500

Child

Line rental and fixed costs for a tariff, includes historic data

reseller codes

2,500

Child

Ofcom reseller identity codes RID and addresses

services

30

Types

Detailed code category

statuses

8

Types

Ofcom Code number status, whether it’s used or not

tariff services

20,000 to 100,000

Child

Tariff service common costs and charging methods, includes historic data

tariff types

12

Types

Tariff categorisation types, including residential or business

tariffs

850 to 5,00

Main

Tariff package names, with monthly cost, operator, rental cost, tariff type and lots of band costs and tariff services in child tables, includes historic data

time bands

4

Types

Time band categorisation


In addition to the main SQL tables, other tables will exist in the magtelecom database to assist with program control.  These include:

Table Name

Description

defaults

Contains various default values

seqnos

Maintains the next available unique id number for a variety of tables.

The Magenta SQL Gazetteer Database offers a Microsoft SQL Server 2008 R2/2012 database with UK locality information, post codes, town names, place names, post code districts and various other gazetteer information.  This database is used to prepare and validate the locality information in the Telecom Database, but may have other purposes. Note there are multiple place or town  name tables from different sources, with differing county names, some with partial old post codes, not all of which will validate against other, since there is no 'official' formalised list of places in the UK.

Note the Gazetteer Database is approximately 1.5 gigs in size and is updated four times a year, when Ordnance Survey issues new  Code-Point Open post codes.

Table Name

Expected Rows

Purpose

Description

place names

80,000

Main

Gazetteer of British place names from Association of British Counties, with traditional and administrative counties

post codes

1,800,000

Main

Full post codes with map and grid references from Ordnance Survey  Code-Point Open

town names

1,600

Main

Post towns and traditional counties from Association of British Counties

place codes

27,000

Main

Old list of places with administrative county and partial post code

post district codes

3,000

Main

Contains every outward post code, giving the post town and county, from web

os place names

260,000

Main

Gazetteer of British place names from Ordnance Survey, with administrative counties, from 1:50,000 scale gazetteer

os locator

850,000

Main

Gazetteer of British road names and localities from Ordnance Survey Locator

os country codes

6

Child

Country codes from Office of National Statistics – UK only

os county codes

40

Child

Local government administrative county codes from Ordnance Survey

os district codes

400

Child

Local government administrative district codes from Ordnance Survey

os ward codes

9000

Child

Local government administrative ward codes from Ordnance Survey

os nhs areas

50

Child

NHS area and region codes from Ordnance Survey


Copyright © 2013 Magenta Systems Ltd, England. All Rights Reserved.