[LA.pm] July Meeting?

Mike Frager mfrager at gmail.com
Mon Jul 11 10:58:03 PDT 2011


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".


-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
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://mail.pm.org/pipermail/losangeles-pm/attachments/20110711/eb460b1a/attachment-0001.html>


More information about the Losangeles-pm mailing list