On Sun, Nov 17, 2013 at 6:57 PM, Tony Theodore <tony.theodore@xxxxxxxxx> wrote:
The current way is much easier since I discovered the “JOIN ... USING(..)” syntax and I’m tempted to try natural joins.
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
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.
> 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.
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.
