Re: [SQL] using constraint based paritioning to fix EAV type schemas? (view inheritance and constraints)

On 7/18/06, Aaron Bono <postgresql(at)aranya(dot)com> wrote:
>
> On 18 Jul 2006 09:07:08 -0700, Andrew Hammond <
> andrew(dot)george(dot)hammond(at)gmail(dot)com> wrote:
>
> > I have a client with the following EAV inspired schema.
> >
> > CREATE TABLE many_tables (
> > table_id text primary key, -- defines which virtual table is
> > encoded
> > attribute1 text,
> > attribute2 text,
> > attribute3 text,
> > attribute4 text,
> > ...
> > );
> >
> > I'd like to use a mix of constraint based paritioning, rules
> > _and_views_ to implement a real schema underneath this mess, like the
> > following.
> >
> > CREATE TABLE cat (
> > cat_id INTEGER PRIMARY KEY,
> > cat_name TEXT NOT NULL,
> > aloofness NUMERIC(1,3) DEFAULT 1.0 CHECK (0.0 <= aloofness AND
> > aloofness <= 1.0)
> > );
> >
> > CREATE RULE many_tables_cat_insert AS
> > ON INSERT TO many_tables WHERE table_id = 'cat' DO INSTEAD
> > INSERT INTO cat (cat_id, cat_name, aloofness) VALUES (
> > CAST(attribute1 AS integer),
> > attribute2,
> > CAST(attribute3 AS numeric(1,3))
> > -- gleefully ignore the other attributes
> > );
> >
> > -- etc for UPDATE, and DELETE rules
> >
> > -- This part doesn't work
> > CREATE VIEW many_tables_a (CHECK (table_id = 'cat')) INHERITS
> > (many_tables) AS
> > SELECT 'cat' AS table_id,
> > CAST(cat_id AS text) AS attribute1,
> > cat_name AS attribute2,
> > CAST(aloofness AS text) AS attribute3,
> > null AS attribute4, ...
> > FROM cat;
> >
> > So, I guess I'm stuck doing the UNION ALL approach in this instance.
> > This won't get me the partitioning win, nor the flexibility and
> > cleanliness of design that I'd get with inheritance.
> >
> > As far as I can see, supporting the above would it mean adding
> > inheritance and constraint support to views. Does anyone have a better
> > approach?
>
>
>
> If you don't mind having redundant data, you could change the ON INSERT
> trigger to copy the data into cat, add an ON UPDATE trigger (you should do
> this anyway) and revoke direct insert/update to cat. Then you don't need
> many_tables_a or a UNION.
>

There's already a performance problem, I suspect that would just exacerbate
it. Since I want to encourage developers to use the relational tables
instead of the many_tables table, refusing DML wouldn't be a good idea.

Of course I don't know if this achieves your intended result or not. What
> is your business requirement for this?
>

The current virtual table design has performance (as well as maitenance)
issues. Performance tuning is problematic. A major re-design of the
application is not something that can be done until the next major release.
However, if I can slide a proper relational schema underneath this
hodge-podge table while retaining compatability for legacy apps then it
becomes possible to fix parts of the application to use the relational
tables incrementally on an as-needed basis.

If I could get constraint based exclusioning to work with the partitioning,
then I would be able to realize performance improvements immediately (which
is always good for a consultant). Unfortunately I don't see any way to do
this. Inheritance seems to fit backwards from what I'm actually trying to
do.

I've seen a few EAV designs in practice. They've all been problematic. I'd
like to have a better way of dealing with them. Which is why I'm tentatively
suggesting support for inheritance and constraints in views. If there's some
other way to achieve constraint based exclusion across a UNION of
heterogenous tables, I'd love to hear it.

Drew