Open topic with navigation

# Set Up a SQL Backend as Fact Store

You can use a SQL database to store facts and qualifiers for your Fact Store.

Answer Server can connect to any RDBMS that supports SQL. You specify how to connect to the database by setting the appropriate connection string in the `ConnectionString` configuration parameter in the FactBank configuration. The most fully tested options are:

- SQLite
- PostgreSQL

The SQL Fact Store has two required tables, one for facts, and one for qualifiers. The facts table stores the values of entity properties. The qualifiers table stores the names and values of particular sets of qualifiers associated with the properties. Each table includes qualifier combination values, which link the properties to the associated qualifiers.

In addition, there are two optional tables, one for sources and one for security types. The sources table stores information about the sources of your facts, including optional security information to allow you to restrict the facts by user permissions. The security types table stores the security configuration information for your security types.

Your Answer Server installation includes PostgreSQL schema files for these tables, and a utility script that you can use to apply these schemas to a database by using the psql utility. These files are available in the `/factbank/schemas/postgresql` directory in your installation.

The following sections describe the tables in more detail, and provide some best practices for how to organize your fact stores.

## Manage Your Tables

Micro Focus recommends that you organize your tables by creating a separate database for each set of facts and qualifiers. In this case, each database is the backend for its own Fact Bank system, which optimizes the database queries required for an `Ask` action.

For example, if you have a collection of facts about company sales histories, and a collection of facts about the products that a company offers, you might create a `sales` database and a `products` database. Each database is a separate Fact Bank instance in your Answer Server setup, and you can easily query one or both, as required.

## Facts Table

The facts table stores the values of entity properties.

This table must have the name `facts`. The facts table has four required columns, and one optional column, described in the following table.

Column | Type | Description
--- | --- | ---
`entity_code` | text | The code for the entity that this row is about, from your `entity_to_code.txt` coding file.
`property_code` | text | The code for the property that this row is about, from your `property_to_code.txt` coding file.
`property_value` | text | The value of this property for the specified entity, in the associated qualifier combination.
`qualifier_combination` | integer | The reference value for the rows in the `qualifiers` table that contain qualifiers that apply to a particular property value. This value corresponds to the values in the `qualifier_combination` column in the `qualifiers` table. Answer Server uses this column to find the appropriate property, entity, or property value when a question contains a particular qualifier. If this value does not correspond to a value in the qualifiers table, Answer Server treats it as having no qualifiers. Micro Focus recommends that you reserve a value to use for properties that do not have qualifiers (usually 0).
`source_id` | integer | (Optional) The reference value for the row in the `sources` table that contains the source for this fact. This value corresponds to the values in the `id` column in the `sources` table. Answer Server uses this column to find and return the source for a particular fact. If this value is missing, or does not correspond to a value in the sources table, Answer Server returns the source as SQLDB. Micro Focus recommends that you reserve a value to use for properties that do not have source information (usually 0).

You can optimize the performance of the facts table by creating indexes on each column. For example, in a PostgreSQL instance, Micro Focus recommends that you create a btree index on each column.

## Qualifiers Table

The qualifiers table stores the codes and values for qualifiers, and the qualifier combination reference that links a qualifier to a row in the `facts` table.

The table must have the name `qualifiers`. The qualifiers table has three columns, described in the following table.

Column | Type | Description
--- | --- | ---
`qualifier_combination` | integer | The qualifier combination reference that identifies qualifiers that are associated with a particular property value for an entity.
`qualifier_code` | text | The code for the property that this qualifier is about, from your `property_to_code.txt` file
`qualifier_value` | text | The value of the qualifier in this qualifier combination

Most data sets will have multiple rows with the same qualifier combination. For example, if your facts table contains the ares of different types of land in a country over time, you might have something like the following table for qualifiers.

qualifier_combination | qualifier_code | qualifier_value
--- | --- | ---
1 | LANDTYPE | Farmland
1 | YEAR | 2015
2 | LANDTYPE | Forest
2 | YEAR | 2015
3 | LANDTYPE | Farmland
3 | YEAR | 2016
4 | LANDTYPE | Forest
4 | YEAR | 2016

In this case, the qualifier combination 1 relates to farmland in 2015, qualifier combination 2 relates to forest in 2015, and so on.

You can optimize the performance of the qualifiers table by creating indexes on each column. For example, in a PostgreSQL instance, Micro Focus recommends that you create a btree index on each column.

## Sources Table

The sources table is an optional table to allow you to store the sources for your facts. You can use this option with the `source_id` column in the facts table to store the details of the fact sources. Answer Server returns the source information with the fact when it returns in an `Ask` action.

The table must have the name `sources`. The sources table has two required columns and two optional columns, described in the following table.

Column | Type | Description
--- | --- | ---
`id` | integer | A primary key integer ID value for the source.
`source` | text | The name of the source. Answer Server returns this value in the `Ask` action when it returns a fact that has the associated source ID.
`acl` | text | (Optional) The Access Control List (ACL) for this source. You can use this option if you want to use user security for your facts.
`security_type_id` | integer | (Optional) The reference value for the row in the `security_types` table that contains the security type for this source. This value corresponds to the values in the `id` column in the `security_types` table. You can use this option if you want to use user security for your facts. Answer Server uses this column to find and return the security type for a particular source. If this value does not correspond to a value in the `security_types` table, Answer Server treats it as having no security. You can set `security_type_id` to `NULL`, or you might want to reserve a value to use for properties that do not have security (usually 0).

## Security_Types Table

The security types table is an optional table to allow you to store the security type information for your sources. You can use this option with the `security_type_id` column in the sources table to store the details of the security types.

The table must have the name `security_types`. The security types table has two columns, described in the following table.

Column | Type | Description
--- | --- | ---
`id` | integer | A primary key integer ID value for the security type. This value corresponds to the IDs that you use in the `sources` table.
`friendly_name` | text | The name of the security type. This value must correspond to the security type configuration section in your Answer Server configuration file.

© 2015-2018 EntIT Software LLC, a Micro Focus company | Send documentation feedback to Micro Focus Close Send documentation feedback To open the configured email client on this computer, open an email window. Otherwise, copy the information below to a web mail client, and send this email to swpdl.idoldocsfeedback@microfocus.com. We appreciate your feedback! Product: Topic Title: Help Topic: Feedback:

_FT_HTML5_bannerTitle.htm