Address
Addresses define where a service is delivered, and since the definition of each of the customers service is done on a "line by line" basis each line stores all the address information. In Telefakt we deal with both A and B addresses, in the case of a service which just provides some connection between two points, think of a customer just buying a fibre cable between two big cities, they can use it for whatever they want, they just rent the cable. We call these FROM and TO addresses, and this is crucial to be able to store all the necessary data about a lines location.
However in Telefakt there is some data duplication happening on lines in an order. Since each line defines their address in its entirety, meaning we don't actually have a table listing all possible addresses and assigning them primary keys which the LINEINFO table then just points to. Each line has all the address information, meaning the LINEINFO table contains all these columns which are used to define addresses. This does give the added option of delivering lines to different locations within one and the same order, however in reality this is never used since it then becomes more benefitial to use the concept of a compound order to link two orders at different locations together instead.
| From address | To address | Comment |
|---|---|---|
| LINE_FROM_COUNTRY | LINE_TO_COUNTRY | Country |
| LINE_FROM_POST_ID | LINE_TO_POST_ID | Postcode |
| LINE_FROM_CITY | LINE_TO_CITY | City |
| LINE_FROM_ADDRESS | LINE_TO_ADDRESS | Street name |
| LINE_FROM_STREET_NUMBER | LINE_TO_STREET_NUMBER | Street number |
| LINE_FROM_STREET_LETTER | LINE_TO_STREET_LETTER | Street letter |
| LINE_FROM_BOLIGNUMMER | LINE_TO_BOLIGNUMMER | Houser number |
| LINE_FROM_FLOOR | LINE_TO_FLOOR | Floor number |
| LINE_FROM_ROOM | LINE_TO_ROOM | Room number |
| LINE_FROM_ADDRESS_ID | LINE_TO_ADDRESS_ID | GAB address ID |
| LINE_FROM_KOMMUNENUMMER | LINE_TO_KOMMUNENUMMER | Municipality number - Norway specific |
| LINE_FROM_GARDSNUMMER | LINE_TO_GARDSNUMMER | Cadastral unit number - Norway specific |
| LINE_FROM_BRUKSNUMMER | LINE_TO_BRUKSNUMMER | Properpty unit number - Norway specific |
| LINE_FROM_FESTENUMMER | LINE_TO_FESTENUMMER | Leasehold number - Norway specific |
| LINE_FROM_SEKSJONSNUMMER | LINE_TO_SEKSJONSNUMMER | Condominium unit number - Norway specific |
| LINE_FROM_UTMZONE | LINE_TO_UTMZONE | UTM zone |
| LINE_FROM_UTME | LINE_TO_UTME | UTM easting |
| LINE_FROM_UTMN | LINE_TO_UTMN | UTM northing |
| LINE_FROM_LAT | LINE_TO_LAT | Latitude |
| LINE_FROM_LON | LINE_TO_LON | Longitude |
We are investigating ways to alleviate this problem but these columns are very widely used and can't be removed, so any fix/change will have to still leave these columns populated with data. We have a LOCATION object which you can initialise with different constructor functions and then use member functions to assign that location to all lines in an order etc. This object then hides all the complexity of the LINE_FROM and LINE_TO columns and present that data in a way that is easier to work with. This object is accesible in the database and is present in views such as API.V_SITE and can be accessed in PL/SQL to perform operations on orders and lines.
To help with validation and data entry there are supporting tables which store records of legal address data. This data is upadet on a quarterly basis with data from SMALLWORLD.
| Table name | Description |
|---|---|
| COUNTRY | Country information |
| POSTCODE | Postcode information such as contry, city and source |
| GABSTREET | Norwegian national registers of addresses |
| MUNICIPALITY | Register of Norwegian municipialities |