in reply to Re^4: How to interpolate sql-output
in thread How to interpolate sql-output

Oh, i think we have a misunderstanding. I wasn't saying the two table structure is bad. I meant that the query requires only one table.

The structure you are using is known as Entity Attribute Value. It is excellent when working with unknown data, though some efforts may be required to keep it working efficiently.

Replies are listed 'Best First'.
Re^6: How to interpolate sql-output
by Seq (Novice) on Jan 15, 2015 at 01:17 UTC

    Ok. Now I see your point more clearly.

    Proper indexing is essential in that key-value-case.

    Actually, that 'parent'-table is needed, cause it contains main activity boolean.
    I just forgot to add that and activity after that variable.

    I had no idea of name of that approach.
    It's just natural derivation after associative arrays and key-values in Memcache etc.

    My fools dream is to put everything to db, even code.
    Just a Apache module and conf to Apache, how to find code.
    I think that it could make updates and backuping easier.
    Ofc that code should be cached, but then servers could be even lighter. Cause it can work without hard drive (or ssd).

    If one, like me, wants to balanced and redundant web-serving it would be great if servers are light and easily swappable.
    Also there are quite significant power savings, when one can shut down some servers in quieter times.

    One can save a lot compared normal one unit server in both power and cooling costs.

[reply]
[d/l]

      I just forgot to add that and activity after that variable.

      Details, details, details... :)

      The query might be better as a join:

      select distinct
    foo.id        key,
    last_value(data.value)
     over
     (
      partition by
        data.foo_id
      order by
        data.id
     )            value
from
    foo,
    foo_data    data
where
        foo.id        <> ?
  and    foo.activity    is true
        —
  and    data.foo_id    = foo.id
  and    data.aktivity    is true
  and    data.key        = 'name';

      [download]

      It's just natural derivation after associative arrays and key-value

      That is great for programming. In the database world, it is generally considered the wrong approach, when the data is known. If data captured is unknown, it is likely the only choice.

      Proper indexing is essential in that key-value-case.

      That will work on small tables. Once it gets large, the index is of little help. Partitioned tables is one way out. In any case, EAVs are best avoided.

      put everything to db, even code.

      People do that, but it is error prone and confusing. Besides, queries can be stored natively in the database as a view or inside a stored procedure. That is what they are there for.

[reply]
[d/l]

        Join might be better than subquery. And as far as I can see, that data.key can be multiple with ors or even in and like/ilike should work also.

        I have tables empty (=just couple of lines) and subquery is faster than window-join. 1.1ms vs 1.4ms. No indexes. Postresql v8.4.22, Debian 6.0.10

        Usually there should be vacuuming of some kind. Just transfer old to history.

        Do you have any projects in mind, where Perl-code is stored in db?

        There is ofc limit how much views etc one should put to db. Sometimes it's better to do with code than in db.
        Views are nice, when you want to limit permission of some user. There are few things in on update and on delete.

[reply]

In Section Seekers of Perl Wisdom