Обсуждение: Composite types or composite keys?

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. 

Thanks,

Tony

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

On 15 Nov 2013, at 8:04 pm, Chris Travers <chris.travers@gmail.com> wrote:
>
> In general, if you don't know you need composite types, you don't want them.  You have basically three options and
theway you are doing it is the most typical solution to the problem 

The current way is much easier since I discovered the “JOIN ... USING(..)” syntax and I’m tempted to try natural joins.

> Having experience with table inheritance and composite types in tuples, I will say the former has fewer sharp corners
thanthe latter.  
>
> Where composite types really work well is where you want to add functions which take the type as input.  In essence
youcan develop some very sophisticated models with them, but you probably would not use them for storage unless you
haveother considerations in mind. 

Thanks for that, I’ve done some reading on inheritance and it looks like I can create an empty parent table that acts
likea column definition template. This also automatically creates a type that can be used in functions which sounds
likewhat I’m after. There are also scenarios where “product” is a combination of “level" and “id” (where “level” can be
thingslike brand/category/sku) and I’d like to use the same calculations regardless of where it sits in the hierarchy. 

Cheers,

Tony

On 16 Nov 2013, at 3:01 am, Merlin Moncure <mmoncure@gmail.com> wrote:
>
> 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

Thanks, I didn’t see any of those - I was thinking that they were like pseudo tables or column templates.

> *) 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.

I’ll give it a try - I might stick to using plain or inherited tables for the main storage and then experiment with
compositetypes for functions and other aggregate tables that are used internally. 

Cheers,

Tony

On 18 Nov 2013, at 2:24 pm, Chris Travers <chris.travers@gmail.com> wrote:
>
> I haven't done work with this so I am not 100% sure but it seems to me based on other uses I have for table
inheritancethat it might work well for enforcing interfaces for natural joins.  The one caveat I can imagine is that
thereare two issues that occur to me there. 
>
> 1.  If you have two child tables which add a column of the same name, then your centralized enforcement gets messed
upand you have a magic join which could take a while to debug.... 
>
> 2.  The same goes if you have two child tables which also inherit a different parent table for a different natural
join....
>
> To be honest I think being explicit about joins is usually a very good thing.

I can see how debugging a magic join would quickly outweigh any benefits and the “USING()” clause nicely reflects the
foreignkey definition, so I’ll stick with explicit joins. 

Thanks,

Tony