Search Postgresql Archives

Re: Composite types or composite keys?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 






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

On 15 Nov 2013, at 8:04 pm, Chris Travers <chris.travers@xxxxxxxxx> 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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux