Skip to content

Properties and Validation

Jump to bottom
Samiwel Thomas edited this page May 10, 2018 · 1 revision

Introduction

This document explores various ways in which Properties and Validation can be implemented within Author.

Scope

This spike will look specifically at answering the following questions:

  • How would a data model for properties and validation look?
  • What types, queries and mutations would be required on the GraphQL API?
  • How should the values be persisted?
  • How can we share certain properties across answer types?
  • How do we provide sensible defaults and allow overrides?
  • How might we enforce only valid properties?
  • How might we model different types of attributes? i.e. Boolean/Numeric/String
  • How can we associate different properties with different types of Answer. e.g. a currency field has decimals and currency properties, whereas number answers only has decimals

How would a data model for properties and validation look?

There's an established pattern for this type of data modelling called the Entity-Attribute-Value model. There's three main parts to this. The Entity that has the property, the property itself (described by an attribute), and the value.

An example would be something like this:

Entity Attribute Value
Answer1 Mandatory true

But this can get more complex, and the attribue itself can be described by a foreign key where additional detail can live about the attribute:

entity_properties

Entity Attribute Value
Answer1 1 true

attributes

Id Name Type Default Validation
1 Mandatory Boolean false null

I think it makes sense to take this established pattern but perhaps tailor it slightly to our needs.

Option 1

We could take the above approach and model the properties using Postgres JSON column.

answers

id label description type properties
1 Annual turnover Gross annual turnover Currency { required: false, decimals: 2, currency: "£" }
2 Years trading Number of years Number { required: false, decimals: 0 }

This model could support default properties and overriding by type with the introduction of a new table for storing default properties.

e.g.

default_properties

id type default_properties
1 Currency { decimals: 0, currency: "£" }

This would allow you to override the defaults by merging the defaults with the overridden values.

const defaultProps = getDefaultProperties(answer.type);
const actualProperties = {
  ...defaultProps,
  ...answer.properties
}

This could even be taken a step further to allow sharing of properties between differnt answer types. For example, in the case of number and currency, they both share required and decimals.

id type inherits_from (fk) default_properties
1 Answer null { required: false }
2 Number 1 { decimals: 0 }
3 Currecny 2 { currency: "£" }

By following the inheritance chain, you could work out that Currency answer types have default properties: { required: false, decimals: 0, currency: "£" }.

The following SQL can be used to create a function that returns the properties hierarchy.

CREATE OR REPLACE FUNCTION find_parents(child_id INT)
  RETURNS TABLE(id INT, parent INT, name VARCHAR(30), value jsonb)
AS
$BODY$

WITH RECURSIVE find_parents AS (
  SELECT
    t1.id as id,
    t1.parent,
    t1.name,
    t1.value
  FROM test_json t1
  where t1.id = child_id
  UNION
  SELECT
    t2.id,
    t2.parent,
    t2.name,
    t2.value
  FROM test_json t2
    INNER JOIN find_parents fp on fp.parent = t2.id
)
select *
from find_parents fp;

$BODY$
LANGUAGE sql;

select * from find_parents(3);

Benefits

  • Can add new properties to Answer types without having to update each single answer.
  • Can model different types relitively easily (JSON types)
  • Can easily get the properties for an answer.
  • Can support overriding and inheriting properties from other base types.
  • Only specific property overrides would be stored against an answer.
  • Easy to see what properties have been changed.
  • Postgres has good support for storing/querying JSON.
  • JSON easy format to work with in JS.

Drawbacks

  • Need to query two tables to merge default and overridden properties.
  • Slightly different style of querying to what currently do (relational model). Possibly some unforseen challenges to overcome as we implement this.

What types, queries and mutations would be required on the GraphQL API?

I expect to be able to query properties like this:

answer {
  id
  properties {
    name
    value
  }
}

This would only require the introduction of a single query resolver for properties. This resolver would be responsible for merging the default property chain with any answer specific overrides.

The type of the property (Bool/String/Number) can be inferred from the value. But if there were a need to avoid determining this in the front end it could be added to the resolver.

The main mutation that would be called by Author would be

{
  mutation updateProperty(input: {
    answerId,
    name,
    value
  }) {
    name,
    value
  }
}

But it would probably be useful for supporting other properties (e.g. qCodes?) if we allowed create and delete mutations as well.

createProperty(input: {
  answerId,
  name,
  value
})

deleteProperty(input: {
  answerId,
  name
})

In both of these cases, the create and delete would only affect the answer-specific overrides and not the underlying default properties.

Although, this would be possible with the introduction of createDefaultProperty, updateDefaultProperty and deleteDefaultProperty, if required.

Validation

I would expect to be able to query validations on an answer.


fragment EarliestDateFragment { }

fragment LatestDateFragment { }

fragment MinDurationFragment { }

fragment MaxDurationFragment { }

fragment NumberValidationFragment {
  ... on NumberValidation {
    minValue
    maxValue
  }
}

fragment DateValidationFragment {
  ... on DateValidation {
    ...EarliestDateFragment
    ...LatestDateFragment
  }
}

fragment DateRangeValidationFragment {
  ... on DateRangeValidation {
    ...EarliestDateFragment
    ...LatestDateFragment
    ...MinDurationFragment
    ...MaxDurationFragment
  }
}


{
  answer(id: 1) {
    label
    type
    validations {
      id
      ...NumberValidationFragment
      ...DateValidationFragment
      ...DateRangeValidationFragment
    }
  }
}

Similar to properties. I think it makes sense to have some kind of default set per answer type.

validation

id type key
1 Answer Number
2 Answer Date
3 Answer DateRange

In future we could support Page based validation with something like...

id type key
4 Page QuestionPage

validation_rules

id name
1 Min value
2 Max value
3 Earliest Date
4 Latest Date
5 Minimum Duration
6 Maximum Duration

validation_validation_rules

id validation_id validation_rule_id enabled
1 1 1 true
2 1 2 true
3 2 3 true
4 2 4 true
5 3 3 true
6 3 4 true
7 3 5 true
8 3 6 true

default_answer_validation

id answer_type validation_id
1 Number 1

validation_rule_default_options

id validation_rule_id options
1 1 { minValue: 0 }
2 2 { maxValue: 9999999999 }
3 3 `{ 
                          earliestDate: {
                            offset: {
                              value: 1,
                              unit: ["years"]
                            },
                            relative: "before",
                            referenceValue: {
                              type: "metdata",
                              value: "start_date"
                            }
                          },
                          latestDate: {
                            offset: {
                              value: [2, 4],
                              unit: ["years", "months"]
                            },
                            relative: "after",
                            referenceValue: {
                              type: "custom",
                              value: "2018-12-25-T00:00:00 UTC"
                            }
                          },
                          }
                         }` |

I'm not sure we should be storing this complex JSON structure against a validation rule.

On the one hand, it's quite flexible and would work for all validation rules (with varying types of validation options). But on the other hand it could become unwieldily and we may need to validate using our own JSON schemas to ensure data in these columns are valid.

My recommendation is to allow work on implementing properties to inform decision whether or not to use JSON on Validations or normalise this into some other validation tables e.g. validation_rule_options

Clone this wiki locally