Re: Composite types or composite keys? - Mailing list pgsql-general

From Chris Travers
Subject Re: Composite types or composite keys?
Date
Msg-id CAKt_ZfuEe4eY-C+=FQbks2RmOTcAowDyq8ETY6deoEdgp-Y=WA@mail.gmail.com
Whole thread Raw
In response to Re: Composite types or composite keys?  (Tony Theodore <tony.theodore@gmail.com>)
Responses Re: Composite types or composite keys?
List pgsql-general



On Sun, Nov 17, 2013 at 6:57 PM, Tony Theodore <tony.theodore@gmail.com> wrote:

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 the way 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 than the latter.
>
> Where composite types really work well is where you want to add functions which take the type as input.  In essence you can develop some very sophisticated models with them, but you probably would not use them for storage unless you have other 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 like a column definition template. This also automatically creates a type that can be used in functions which sounds like what I’m after. There are also scenarios where “product” is a combination of “level" and “id” (where “level” can be things like brand/category/sku) and I’d like to use the same calculations regardless of where it sits in the hierarchy.

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 inheritance that it might work well for enforcing interfaces for natural joins.  The one caveat I can imagine is that there are 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 up and 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.

Best Wishes,
Chris Travers

Cheers,

Tony




--
Best Wishes,
Chris Travers

Efficito:  Hosted Accounting and ERP.  Robust and Flexible.  No vendor lock-in.

pgsql-general by date:

Previous
From: Ken Tanzer
Date:
Subject: Re: What does this error message mean?
Next
From: Hengky Liwandouw
Date:
Subject: Sum 2 tables based on key from other table