Re: Composite types or composite keys?

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

>
> On 15 Nov 2013, at 8:04 pm, Chris Travers <chris(dot)travers(at)gmail(dot)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.
http://www.efficito.com/learn_more.shtml