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

The other table is simply not required. It contains no information that the inner table does not have. The only benefit it might have is id is already unique, but at the cost of loading another table and doing a join, it probably hurts more than it helps.

Queries get more and more complicated over time as they are used for more and more things. This adds complexity which makes understanding the queries take longer and hampers their revision. The simpler the query, the easier on the programmer and the database's optimizer.

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

    Let's say that I have table ppl, which includes running id, activity boolean and timestamp defaulting to now.
    Then I have another table (=ppl_data) where is also id as serial (only for easier manipulation), ppl_id, key and value and usually timestamp.

    I can put anything to that ppl_data-table without altering db-schema, cause it is in key-value-pair.

    Cause I use only inserts and fetching only newest data with that key, there is nice history of changes.

    Reason to this is, that customer is able to add different fields if needed.
    Ofc there is also datatype for that field, so sanity-check and casting is easier.

    If needed one can vacuum old data from that ppl_data, if it affects to db-server, but usually there is enough power.
    As log as that db-server isn't virtual-server where disk-latency might cause slowing downs eventually.

    And when there is also way to do different queries, one can alter or even add features without code-changes.

[reply]

      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.

[reply]

        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]

In Section Seekers of Perl Wisdom