Re: Enforcing referential integrity against a HSTORE column

On 01/02/2016 08:13 AM, Dane Foster wrote:

Ccing list.

On Sat, Jan 2, 2016 at 10:30 AM, Adrian Klaver
<adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> wrote:

    On 01/01/2016 07:47 PM, Dane Foster wrote:

        Hello,

        I'm moving a MySQL database to PostgreSQL and redesigning parts
        of it to
        take advantage of PostgreSQL's richer type system and other advance
        features. Currently I am attempting to replace a table of name/value
        pair data w/ a hstore column. But now that the data will no
        longer be
        flattened out in a table I need to manually handle referential
        integrity


    And the benefit is?
    ​

​
The benefit is supposed to be client side simplicity. The data in these
particular tables are ultimately consumed by JavaScript as JSON on the
front end to populate/maintain a dynamic HTML for​m. So I was attempting
to build a model that more closely reflects how the data is used because
the people using the data aren't SQL folks and the code that converts
the data from table/rows to JSON is not straight forward for my audience.

In that case you may want to look at the JSON types, json and/or jsonb(depending on Postgres version):


http://www.postgresql.org/docs/9.4/interactive/datatype-json.html

--
Adrian Klaver
adrian.klaver@aklaver.com