[LA.pm] July Meeting?

Ben Tilly btilly at gmail.com
Mon Jul 11 11:28:55 PDT 2011


2011/7/11 Mike Frager <mfrager at gmail.com>:
> Actually vertical tables are not much like pivot tables. They are a way to
> store data where the schema (the columns names) changes frequently or is not
> known ahead of time.
>
> This might give away the presentation, but here's how it works:
>
> You create one table to store the field names:
>
> Field Names Table:
>    id
>    field_name
>
> Then another table for the data:
>
> Data Table:
>   id
>   record_id
>   field_id
>   data
>
> Each "record" in the vertical table, gets broken down into multiple rows in
> the "Data" table. Each row in the "Data" table then references a "Field"
> type and contains a piece of data. This is the equivalent of a column value
> in a regular, horizontal table. These records are tied together into on
> "virtual row" by the "record_id".

Transactional performance may be OK, but dynamic creation of complex
reports would really, really suck with a schema like that in a
traditional relational database.

(If you're going outside of relational databases, then map-reduce
should work just fine.  Albeit with a significant necessary resource
overhead.)

> -Mike Frager
>
>
> On Mon, Jul 11, 2011 at 10:44 AM, Randal L. Schwartz <merlyn at stonehenge.com>
> wrote:
>>
>> >>>>> "Mike" == Mike Frager <mfrager at gmail.com> writes:
>>
>> Mike>  By doing this you can change the schema on the fly.
>>
>> I've always wanted my flies to have different schemas!
>>
>> Mike> It's likely something you may have seen before, but under a
>> Mike> different name.  Usually worth a look though. Since I first
>> Mike> learned about it, it has greatly helped how I think about
>> Mike> databases.
>>
>> Pivot tables, maybe?
>>
>> --
>> Randal L. Schwartz - Stonehenge Consulting Services, Inc. - +1 503 777
>> 0095
>> <merlyn at stonehenge.com> <URL:http://www.stonehenge.com/merlyn/>
>> Smalltalk/Perl/Unix consulting, Technical writing, Comedy, etc. etc.
>> See http://methodsandmessages.posterous.com/ for Smalltalk discussion
>
>
> _______________________________________________
> Losangeles-pm mailing list
> Losangeles-pm at pm.org
> http://mail.pm.org/mailman/listinfo/losangeles-pm
>


More information about the Losangeles-pm mailing list