Re: Composite types or composite keys?

On Fri, Nov 15, 2013 at 2:01 AM, Tony Theodore <tony.theodore@gmail.com> wrote:
> Hi,
>
> I was reading about composite types and wondering if I should use them instead of composite keys. I currently have
tableslike this: 
>
> create table products (
>         source_system text,
>         product_id text,
>         description text,
>         ...
>         primary key (source_system, product_id)
> );
> create table inventory (
>         source_system text,
>         product_id text,
>         qty int,
>         ...
>         foreign key (source_system, product_id) references products
> );
>
>
> and it means having to add the “source_system" column to many queries. Would something like:
>
> create type product as (
>         source_system text,
>         product_id text
> );
> create table products (
>         product product,
>         description text,
>         ...
>         primary key(product)
> );
> create table inventory (
>         product product,
>         qty numeric,
>         ...
>         foreign key (product) references products
> );
>
> be a correct use of composite types? I rarely need to see the columns separately, so having to write
“(product).product_id”won’t happen much in practice. 

Well, here are the downsides.  Composite types:
*) are more than the sum of their parts performance-wise.  So there is
a storage penalty in both the heap and the index
*) can't leverage indexes that are querying only part of the key
*) will defeat the implicit 'per column NOT NULL constraint' of the primary keys
*) are not very well supported in certain clients -- for example JAVA.
 you can always deal with them as text, but that can be a headache.

...plus some other things I didn't think about.  If you can deal with
those constraints, it might be interesting to try a limited
experiment.   The big upside of composite types is that you can add
attributes on the fly without rebuilding the index.  Test carefully.

merlin