This article in Russian: Промежуточная схема данных для миграции |
ER-diagram of the intermediate schema tables is available here: https://dbdiagram.io/d/5fca9ba19a6c525a03b9da84 |
Data migration from other billing systems is carried out via making tables for an intermediate dataset in the CSV format.
Example — file ACCOUNTS.csv:
"ID";"USER_ID";"ACC_NUM";"ACC_TYPE_ID";"CUR_ID";"BANK_ID";"BALANCE";"CREDIT";"CREDIT_END_DATE";"BALANCE_DATE"
"1001";"101";"123456";"1";"1";"";"926,07";"500";"";"23.09.2017 23:59:59"
Next, using a special tool, the CSV files are uploaded to the Hydra Billing for further reference data matching and the actual database migration.
For the fields of Date and Time type, two formats are possible:
Date type fields accept only one format: |
You can add the REM custom field to each table for exporting useful text data. The data exported into it will become a comment to the corresponding entity: a customer, an equipment, a contract, etc. |
The primary key in each table is always named ID |
Listed in this section are the tables with the data which is not migrated, but is used for matching the values in the Migration application of the system being migrated against customer statuses in Hydra.
The STATUS table
Field | Description |
---|---|
ID | The ID of a status |
NAME | The name of a status |
In the simplest case, there are only two statuses in the registry (activated, deactivated).
The NS_LIST table
ID | The ID of a network service |
NAME | The name of a network service |
Note: this registry is created manually and contains a list of network services: RADIUS, Customer Self-Care Portal, telephony, email, etc. To access them, a customer requires a password and a login.
The UNITS_LIST table
ID | The ID of a unit |
NAME | The name of a unit |
Note: the values are matched against the units registry in Hydra.
The SERVICES table
ID | The service ID (the price plan ID) |
NAME | The service name (the price plan name) |
TYPE | The price plan feature (Y for a price plan; N for a service) |
UNIT_ID | The unit ID — with the reference to UNITS_LIST.ID |
Note: the values stored in this registry are not migrated, but they are matched against the Product catalog entries (TYPE = "Y"
entries are to be matched with Everything → Price plans group items, and TYPE = "N"
entries — with Everything → Services ones).
The CURRENCY table
ID | The currency ID |
NAME | The currency name |
Note: the values are matched against the units registry in Hydra.
The EQUIP_TYPE table
ID | The type ID |
NAME | The equipment name |
Note: the values are matched against the product catalog entries, typically against those in the Active equipment section.
The OP_EQUIP table
Field | Description | Comment |
ID | The equipment ID | |
EQUIP_TYPE_ID | The equipment type ID | A link to EQUIP_TYPE.ID |
CODE | The equipment code | |
IP | The management IP address | |
FIRM_ID | The division ID | A link to FIRMS.ID . Used only in case of a multi-subsidiary migration |
Note: The table is used for correct bindings to provider equipment already exported into the system. When starting the migration section of the provider equipment the matching is carried out based on the exported data.
The GROUPS table
ID | The group ID |
NAME | The group name |
Note: the values are matched against the groups registry. If there are no groups in your system, it still makes sense to add such values in the registry, and bind all customers to groups depending on their categories, for example, Individuals, Organizations, Employees, etc.
The BANKS table
ID | The bank ID |
NAME | The bank name |
Note: the values are matched against the bank registry. Consequently, the banks that come up while exporting should be added into Hydra manually.
The ACC_TYPES table
ID | The account type ID |
NAME | The account type |
Note: the values are matched against the account types registry.
The AUTH_DOC_TYPES table
ID | The document type ID |
NAME | The document type |
Note: the values are matched against the identification document types registry
When setting up correct default control values, it is not obligatory to export tables from this section.
The PHONE_TYPES table
ID | The phone number binding type ID |
NAME | The binding type (Mobile, Home, Office, etc.) |
Note: the values are matched against the address types registry.
The FACT_ADDR_BIND_TYPES table
ID | The street address binding type ID |
NAME | The binding type (Street, Service address, etc.) |
Note: the values are matched against the address types registry.
The PAYMENT_TYPE table
ID | The payment type ID |
NAME | The payment type name |
VIRTUAL | Select "Y" if this is an adjustment payment |
Note: the registry is used for differentiating payments of different types (adjustment payments, cash, bank transfer, JSIP, Visa, Webmoney, etc.) which is useful for creating reports.
In case of a multi-subsidiary structure, you should use the subsidiaries registry to which the FIRM_ID fields in the USERS and OP_EQUIP tables are linked.
The FIRMS table
ID | The subsidiary ID |
NAME | The subsidiary name |
It is matched against the company structure.
Use the data in the following tables when migrating to create entities of the necessary type.
The USERS table
Field | Description | Comment |
ID | The customer ID | |
STATUS_ID | The customer status ID | A link to STATUS.ID. A basic subject is always created with the Active status. |
BASE_ID | The basic subject ID | A link to USERS.ID of a basic subject (a separate entry in the table). It is usually exported empty in order to create bindings automatically. |
LOGIN | The customer code | A unique short name used to identify a customer. |
JUR | Individual or organization (0 — individual, 1 — organization). | |
NAME | Full name (for an individual)/ Name (for an organization). | |
ADDR | The street address format is: <City>,<Street>,<Building>,<Entrance>,<Floor>,<Apartment>,<Intercom code> | If some data is not available then leave empty between commas, for example, Pittsburgh city of, Harrow Hill Rd., 5,,,78,5apt234 The street number can be used together with the building number: Pittsburgh city of, Harrow Hill Rd., 5 bld. 7 ,,,78,5apt234 |
ADDR_REM | Comments to a subject's address | |
AUTH_DOC_TYPE_ID | The ID document type | A link to AUTH_DOC_TYPES.ID |
AUTH_DOC_SERIAL | The document series | |
AUTH_DOC_NO | The document number | |
AUTH_DOC_DATE | The document issue date | The DD.MM.YYYY date format. |
AUTH_DOC_ISSUE | Issued at/by | |
D_BIRTH | The date of birth | The DD.MM.YYYY date format. |
BIRTH_PLACE | The place of birth | |
INN | Individual Taxpayer Identification Number (ITPN / TIN / Tax Id) | |
OPF | The legal form of an organization, for example, sole proprietorship, LLC | The legal forms are not matched. The value from the legal forms registry is matched by the name. |
W_PHONE | Contact office phone number | Digits only, starting with a country code, e.g. 78122128506. It is possible to enter several numbers separated by a comma. |
H_PHONE | Contact home phone number | Digits only, starting with a country code, e.g. 78122128506. Only for individuals that have 0 in the JUR column. It is possible to enter several numbers separated by a comma. |
M_PHONE | Contact mobile phone number | Digits only, starting with a country code, e.g. 78122128506. It is possible to enter several numbers separated by a comma. |
Contact Email address | It is possible to enter several addresses separated by a comma. | |
FIRM_ID | The subsidiary ID | The link to FIRMS.ID. Is used only for migration with multiple subsidiaries. |
Note: the list of customer personal data attributes is extendable if necessary.
The ACCOUNTS table
Field | Description | Comment |
ID | The account ID | |
USER_ID | The customer ID | A link to USERS.ID |
ACC_NUM | The account number | |
ACC_TYPE_ID | The account type ID (a personal account, an operating account, etc.) | A link to ACC_TYPES.ID |
CUR_ID | The currency ID | A link to CURRENCY.ID (if the system uses only the Russian ruble, then it is not necessary to specify) |
BANK_ID | The bank ID | A link to BANKS.ID (for personal accounts - leave empty). |
BALANCE | The account balance | |
CREDIT | The current credit of a customer | Fill in if a credit is required. If not — leave empty. |
CREDIT_END_DATE | The credit limit end date and time. | If empty, but the credit value is entered in CREDIT, then the credit is permanent. |
BALANCE_DATE | The date and time for the balance to be calculated. | DD.MM.YYYY HH24:MI:SS |
The GROUPLINK table
Field | Description | Comment |
ID | The linking record ID | |
USER_ID | The customer ID | A link to USERS.ID |
GROUP_ID | The group ID | A link to GROUPS.ID |
MAIN | The main group feature - 'Y' | It is used to mark the main group when linking a customer to several groups. There should be only one main group. |
Note: if there are no groups in the system used for exporting, then at least you should divide all exported customers into three groups: organizations, individuals, and employees. I.e. you need to add these three groups to the GROUPS table and specify links to them (specify who to add to a group according to certain criteria when exporting)
The EQUIP table
Field | Description | Comment |
ID | The equipment ID | |
USER_ID | The customer ID | A link to USERS.ID |
EQUIP_TYPE_ID | The CPE type ID | A link to EQUIP_TYPE.ID |
OP_EQUIP_ID | Provider equipment ID | A link to OP_EQUIP.ID |
OP_EQUIP_PORT | Provider equipment port number (code) | |
OP_EQUIP_PORT_TYPE | Provider equipment type code | The value is matched by code as similar port types of different equipment are considered different entities in the system structure |
NS_ID | ID of the network service for equipment management | A link to NS_LIST.ID. Typically not used - leave empty |
CODE | The equipment code | A unique short name used to identify equipment |
MAC | The MAC address formatted as | Separate multiple values with a comma |
IP | The IPv4 address or subnet (CIDR notation) | Separate multiple values with a comma |
IP6 | The IPv6 prefix | Separate multiple values with a comma |
PHONE | The customer phone number | Digits only, starting with a country code, e.g. 78122128506. Separate multiple values with a comma |
ADDR | The service address | The address is unloaded in the same format as USERS.ADDR |
ADDR_REM | Comments to the service address |
Note: OP_EQUIP_ID, OP_EQUIP_PORT, OP_EQUIP_PORT_TYPE fields are used for specifying the CPE binding to provider’s equipment and are optional.
The NETSERV table
Field | Description | Comment |
ID | The binding ID | |
USER_ID | The customer ID | A link to USERS.ID |
NS_ID | The network service ID | A link to NS_LIST.ID |
EQUIP_ID | The customer equipment ID | A link to EQUIP.ID |
LOGIN | The login | The login should be unique in terms of the network service. |
PASSWORD | The password | Plaintext password or its hash. |
PASS_TYPE | The password hashing type | Leave empty for plaintext. Encryption types are not matched. The value from the registry of supported hash types for passwords is matched according to the name: SHA1, MD5, etc. |
Note: the login and passwords for VPN, Customer Self-Care Portal, SIP account and other resources controlled by the Billing are exported into this table. For security reasons, during test exporting, passwords can be substituted with asterisks or random rows. Authentic passwords are to be exported only once, during the final migration.
The CONTRACTS table
Field | Description | Comment |
ID | The contract ID | |
USER_ID | The customer ID | A link to USERS.ID |
NUM | The contract number | |
D_DOC | The date of the contract | |
D_BEGIN | The begin date of the contract | |
D_END | The end date of a contract |
The SUBSCR table
Field | Description | Comment |
ID | The subscription ID | |
ACC_ID | The account ID | A link to ACCOUNTS.ID |
CONTRACT_ID | The contract ID | A link to CONTRACTS.ID |
TARIFF_ID | The service (price plan) ID | A link to SERVICES.ID |
EQUIP_ID | The customer equipment ID | A link to EQUIP.ID |
D_BEGIN | The subscription begin date and time | The date and time when a customer subscribed to this price plan or service |
D_END | The subscription end date and time | Leave empty if this is the latest chosen price plan |
QUANT | The scheduled amount of service | For price plans - leave empty |
PAY_DAY | The charging day | When the charging day is not fixed, leave empty |
Note: services and the price plan are exported for the next period. If you have a fixed charging day bound to the 1st day of a month, then export as PAY_DAY = 1 (typically used by organizations).
The PAYMENTS table
Field | Description | Comment |
ID | The operation ID | |
ACC_ID | The personal account ID | A link to ACCOUNTS.ID |
BANK_ID | The bank ID | A link to BANKS.ID. The provider is to have an operating account at this bank. |
D_OPER | The date and time of the operation (payment) | |
PAYMENT_SUM | The amount of payment multiplied by 100 (an even number) | |
PAYMENT_TYPE_ID | The payment type | A link to PAYMENT_TYPE.ID. When left empty, the default payment types are used |
The WRITEOFFS table
Field | Description | Comment |
ID | The operation ID | |
ACC_ID | The account ID | A link to ACCOUNTS.ID |
CONTRACT_ID | The contract ID | A link to CONTRACTS.ID |
D_OPER | The date and time of the operation (charging) | It must lie within the charging period specified in D_BEGIN и D_END |
TARIFF_ID | The service (price plan) ID | A link to SERVICES.ID |
EQUIP_ID | The CPE ID | A link to EQUIP.ID |
SUM | The charged amount multiplied by 100 | The total amount to be charged, not the service price. |
D_BEGIN | The begin date and time of the charging period | |
D_END | The end date and time of the charging period | |
USED | The amount multiplied by 100 | In measuring units specified for the service as per TARIFF_ID entered in Hydra |
The amounts in the WRITEOFFS table should not be negative - only positive or 0. This table is used for exporting chargings over previous charging periods (executed charge logs) and details on current billing period of customers (charge logs in force).
Loading multiple comments to customers and CPE is supported.
The COMMENT_TYPES table
ID | The comment type ID |
NAME | The comment name |
It is matched against the Comment Types Registry
The USER_COMMENTS table
ID | Идентификатор |
USER_ID | A link to USERS.ID |
COMMENT_TYPE_ID | A link to COMMENT_TYPES.ID |
CL_COMMENT | The comment text |
D_OPER | The date and time of creation |
D_SIGNAL | The reminder date and time |
D_EXEC | The execution date and time |
The EQUIP_COMMENTS table
ID | The identifier |
EQUIP_ID | A link to EQUIP.ID |
COMMENT_TYPE_ID | A link to COMMENT_TYPES.ID |
CL_COMMENT | The comment text |
D_OPER | The date and time of creation |
D_SIGNAL | The reminder date and time |
D_EXEC | The execution date and time |
During the migration process some of the fields are decomposed, and the data from them is recorded into additional tables. Particularly, an address in USERS.ADDR undergoes parsing and is then recorded in the PARSED_USER_ADDRESSES table. Thus, if the billing that undergoes data exporting stores decomposed information on the address it is not necessary to write it in one row as you can fill in the table instead.
The PARSED_USER_ADDRESSES table
Field | Description | Comment |
USER_ID | The corresponding ID from the USERS table | A link to USERS.ID |
ADDR_BIND_TYPE_ID | The address type | A link to FACT_ADDR_BIND_TYPES.ID An optional field. By default, the actual address is used. |
VC_ORIGINAL | The row with the address from USERS.ADDR. When filling in manually it is optional. | |
VC_DISTRICT | District | Fixed type of region – with code REGION_TYPE_District |
VC_CITY | The locality name | |
VC_CITY_TYPE | The locality type | A short name of the region type:"c.", "t."... |
VC_STREET | The street name | |
VC_STREET_TYPE | The street type | A short name of the region type:"str.", "av.."... |
VC_HOUSE | The street number | |
VC_BUILDING | The pavillion number | |
VC_CONSTRUCT | The structure number | |
VC_OWNERSHIP | The property number | |
VC_ENTRANCE_NO | The entrance number | |
VC_FLOOR | The floor | Only numeric values allowed |
VC_FLAT | The apartment | |
VC_DIS_CODE | The intercom code | |
VC_CUSTOM_ADDRESS | The custom address | |
VC_REM | The comment |
The PARSED_EQUIP_ADDRESSES table
Field | Description | Comment |
EQUIP_ID | The corresponding ID from the EQUIP table | A link to EQUIP.ID |
ADDR_BIND_TYPE_ID | The address type | A link to FACT_ADDR_BIND_TYPES.ID. An optional field. By default, the service address is used. |
VC_ORIGINAL | The row with the address from EQUIP.ADDR. When filling in manually it is optional. | |
VC_DISTRICT | District | Fixed type of region – with code REGION_TYPE_District |
VC_CITY | The locality name | |
VC_CITY_TYPE | The locality type | A short name of the region type:"c.", "t."... |
VC_STREET | The street name | |
VC_STREET_TYPE | The street type | A short name of the region type:"str.", "av.."... |
VC_HOUSE | The street number | |
VC_BUILDING | The pavillion number | |
VC_CONSTRUCT | The structure number | |
VC_OWNERSHIP | The property number | |
VC_ENTRANCE_NO | The entrance number | |
VC_FLOOR | The floor | Only numeric values allowed |
VC_FLAT | The apartment | |
VC_DIS_CODE | The intercom code | |
VC_CUSTOM_ADDRESS | The custom address | |
VC_REM | The comment |
The USER_PHONES table
USER_ID | The corresponding ID from the USERS table |
PHONE_TYPE_ID | The corresponding ID of the phone number binding type to the subject from the PHONE_TYPES table |
PHONE | The phone number |
REM | The comment |
During the latter migration it may be useful to load the customer contents from the exported data into the contents of the already existing customer in Hydra. To do so you should fill in the USER_MAPPINGS
table.
The USER_MAPPINGS table
USER_ID | The ID of the exported customer from the USERS table |
USER_DST_CODE | The existing customer code from Hydra |
Oftentimes, the number of employees is quite big to be entered manually. You need to do it by adding employees as customers into the USERS table, then granting the permission to access applications with the help of NETSERV
(similarly to granting them access to the Customer Self-Care Portal) and filling in the following tables.
The DEPARTMENTS table
Field | Description |
ID | The department identifier |
FIRM_ID | A link to the division from FIRMS.ID |
NAME | The department name |
The EMPLOYEE_TYPES table
Field | Description |
ID | The identifier |
NAME | The name |
The EMPLOYEES table
Field | Description | Comment |
ID | The identifier | |
USER_ID | The customer | A link to USERS.ID |
FIRM_ID | The employer division of the customer | A link to FIRMS.ID |
DEPARTMENT_ID | The department (can be used in Hydra to distinguish permissions) | A link to DEPARTMENTS.ID |
EMPLOYEE_TYPE_ID | The employee type (can be used in Hydra to distinguish permissions) | A link to EMPLOYEE_TYPES.ID |
NAME_GENITIVE | The full name | |
APPOINTMENT | The position | |
APPOINTMENT_GENITIVE | The position | |
ACT_AUTHORITY | Acting on the basis of authority | |
TAB_NO | The employee code | |
D_BEGIN | The date of hire | |
D_FIRE | The date of leaving | |
REM | The comment |