Charles Tassell wrote:
> Hi all,
>
> In a project I'm working on at the moment, I want to store multiple
> pieces of information about an item, and I'm looking for a good way to
> structure my tables. The items will have a variable number of descriptive
> attributes (ie, size, color, logo) so I was thinking of using arrays, but I
> don't think I can get the select options to work the way I want.
You are working with a relational database, so you need to follow relational
database rules. What this means is that you should normalize your tables
according to the rules laid down by E.F. Codd. Best find a good book about
relational databases.
The first rule is that repeating fields should be eliminated. As arrays count
as repeating fields, they should be eliminated as much as possible (that is
one reason I find Oracle good : it doesn't support arrays (at least version 7
didn't)). Multiple fields with the same meaning should be added by using
another table :
[Item Name][Item Key] -->> [Item Key][TypeKey][Value Key]
[Type Key]-->[Type Key][op_name]
[Value Key]-->[Value Key][op_label]
> For
> instance, with the following tables:
>
> CREATE TABLE test (
> item_name text,
> op_types int4[],
> op_values int4[]
> );
>
> CREATE TABLE option_types (
> op_ndx serial,
> op_name text -- A description of this option type. ie "Size", "Color"
> );
>
> CREATE TABLE option_values (
> op_ndx int4,
> op_value int4, -- op_ndx and op_value make up a unique id
> op_label text -- the label for this value, ie "Red", "Blue"...
> );
>
> I can't figure out how to do a single SELECT that will give me item_name
> and all op_name and op_label's for everything in the array. It doesn't
> help that www.postgresql.org is down, so I can't read Bruce's chapter on
> arrays at the moment. :)
>
> Is what I'm looking to do possible, or do I have to do a SELECT to get my
> array info, and then open up another query to get the labels? Also, how
> well are arrays supported in languages like PERL+DBI and PHP? Not much
> sense getting a really cool schema set up if I can't use it. :-)
So, what you'll have is four tables instead of three, and then it is possible
to get your data with one single select statement or by defining a view.
Arrays are really only good for processing by a normal processing language,
they are not suited for use with pure SQL.
Good luck.
Jurgen Defurne
defurnj@glo.be