Re: COPY to table with array columns (Longish)

So how about creating a sys table too:

SYS  sys_id

ZKCOST  sys_id,  zkcost_id,  zkcost_value

and

ZPRECMPL  sys_id,  zprecmpl_id,  zprecmpl_value

This gives you the flexibility to expand to as many "columns" for
ZPRECMPL as you want.  The bottom line is, I think it would be much
more efficient storage to determine a way to turn your variable number
of columns into rows of a value table.

For example, I have a web site for role playing games.  Since each
game has different attributes for the characters you play, I need a
flexible way to define the list of attributes and then allow people to
enter the values of those attributes.  Below is a simplified version
of my table structure:

attribute  attribute_id (PK),  attribute_name

character  character_id (PK),  character_name

character_attribute  character_attribute_id (PK),  character_id (FK),  attribute_id (FK),  value

It is a little different than your problem but demonstrates how a
variable number of columns (in this case a variable number of
attributes for a character) can be stored with one row representing
each column.

Because I don't understand the context of your problem as well as you
do, you will probably have to determine how to tweak this to meet your
needs.  But I think, from the information you have provided, that this
"pivoted" table approach will work for you.

-Aaron

On 6/13/06, Phillip Smith <phillips@weatherbeeta.com.au> wrote:
> Not quite... ZKCOST and ZPRECMPL are two completely different things. They
> have no relation except they're both stored in the SYS table in D3.
>
> If we put it in a tree:
>         SYS
>          |
>          +- ZKCOST
>          |     \- <value>
>          |
>          +- ZPRECMPL
>          |     +- <value>
>          |     +- <value>
>          |     +- <value>
>          |     \- <value>
>
> or table:
>         SYS
>         +-----------+---------+---------+---------+---------+
>         | ZKCOST    | <value> |         |         |         |
>         | ZPRECMPL  | <value> | <value> | <value> | <value> |
>         +-----------+---------+---------+---------+---------+