dbConventions

Fact table naming requirements

• The name of the table must start with “fact_”. The Quickforms API automatically appends the prefix to all DAO calls, so all references to the fact table must omit the “fact_” prefix.
• There must be a key column named “<factname>Key” where <factname> is the name of the fact without the prefix. This column must be an integer, and must auto increment.
• There must be a deletion column named deleteFlag that defaults to 0. This column is used for the built in delete button support.
• There are three optional columns to collect metadata about the fact insertions.
  • addedBy (integer): references to the current user of the application who submitted the fact.
  • createdDate (datetime): references to the server time when the fact was submitted for the first time.
  • updatedDate (datetime): references to the server time for every subsequent update to the form. On a new entry, it defaults to the createdDate.
• Indexes into lookup tables must have the same name as the lookup table without the prefix. For example, a column named exampleLookup would index into the table lkup_exampleLookup. This column must be an integer.
• Indexes into bridge tables have the same rules as lookup tables. There must also be a “Multi” postfix.
• If there is a field that is used for measures in the enterprise reporting suite, but also has an associated lookup table, two columns are required. The first column is the standard lookup index column with the regular conventions. The second column has the same name as the first, with the extra “Measure” postfix. The data type of this variable must be the same as the “Label” column in the lookup table.

Lookup table naming requirements

• The name of the table must start with “lkup_”. The Quickforms API automatically appends the prefix to all DAO calls, so all references to the lookup table must omit the “lkup_” prefix.
• There are three required columns as follows:
  • <lookupName>Key (integer). Cannot auto increment. The primary key for the table.
  • <lookupName>Label (varchar, number, …). This is the value displayed in the view, and the value stored in the fact table if this dropdown is a measure.
  • <lookupName>Order (integer). The lookup values are automatically sorted based on this column. Negative number entries in this column are omitted from the result set.
• Extra columns are discouraged as it disables the in-app design module included in the Quickforms JS API. In this case, all edits to the lookup table must be performed through the excel upload and download interface.

Bridge table naming requirements

• The name of the table must start with “lkup_”. The Quickforms API automatically appends the prefix to all DAO calls, so all references to the bridge table must omit the “lkup_” prefix. In addition, there must also be a “Multi” postfix. The Quickforms API does not automatically append this postfix. The name of the bridge table (without the multi postfix) must be the same as the name of the corresponding lookup table.
• There are two required columns as follows:
  • <bridgeName>Key (integer). The index into the fact table
  • <bridgeName>Value (integer). The index into the lookup table
• Extra columns are not supported by the Quickforms API.