From: Aaron Bono <postgresql@aranya.com>
To: phillips@weatherbeeta.com.au
Cc: pgsql-sql@postgresql.org
Subject: Re: COPY to table with array columns (Longish)
Date: Tue, 13 Jun 2006 10:05:04 -0500
Message-ID: <bf05e51c0606130805r452fec46y1ef96a371f82b741@mail.gmail.com> (raw)
In-Reply-To: <009801c68eaa$5bf73530$9b0014ac@ITPhil>
References: <bf05e51c0606122111m78e56216p3f0515cb5885fe1f@mail.gmail.com>
<009801c68eaa$5bf73530$9b0014ac@ITPhil>
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> |
> +-----------+---------+---------+---------+---------+
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: postgresql@aranya.com, phillips@weatherbeeta.com.au
Subject: Re: COPY to table with array columns (Longish)
In-Reply-To: <bf05e51c0606130805r452fec46y1ef96a371f82b741@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox