Re: Composite type

On Wed, Jan 29, 2014 at 1:44 PM, George Ant <g(dot)antonopoulos000(at)gmail(dot)com>wrote:

> Guys thank you for your replies, you really helped me a lot!!!
>
> I haven't use Postgres before and its the first time I am "playing" with
> composite types, so sorry if I hurted your eyes with my question!
>
> Kevin I followed your suggestion and seems to work fine.I think it is what
> I was looking for.
>
> I am actually trying to transform a RELATIONAL database to
> OBJECT-RELATIONAL. Can anybody provide me with any helpful link or suggest
> me some "must do" steps? (maybe I need to start a new post for this).
>

Huge question. You might find my blog (http://ledgersmbdev.blogspot.com)
to be of interest.

Here's the very brief overview.

1. Every good object-relational design is also good relational design to
at least a certain extent. With a few exceptions (which, if they don't
strike you as necessary, they probably aren't exceptions), you don't want
to sacrifice relational design in the process. Usually if you are going to
sacrifice relational design (up to and including 1st Normal Form) you
should have a very good reason to do so. Sacrificing relational design
always has costs. Don't do it willy nilly.

2. Table inheritance in PostgreSQL works better when you think less in
terms of inheritance and more in terms of mix-ins. This is because certain
things, like keys, don't inherit the way you'd expect in an inheritance
approach. Table inheritance in PostgreSQL is a wonderful tool for
maintaining consistent interfaces across necessarily different tables (for
example because foreign keys are different). However it doesn't work
flawlessly for things like managing subtypes. Use joins (and maybe views)
for that.

3. You can treat tables as classes, and build methods on those. You can
also treat views as classes and build methods on those. This allows you to
encapsulate your data in other ways. Complex types can be classes too.

I usually think about object-relational design not in terms of the table
layout but in terms of certain styles of writing functions and ways of
interacting with the underlying data. In other words, for me it is about
ways of encapsulating logic both above and below SQL so that various kinds
of complex operations are gracefully handled with looser coupling between
the application and the database.

Going very far on this will require writing client libraries. I am working
on some in Perl. I would be happy to collaborate with those in other
languages. However it's best to start small, and experiment with adding a
little bit at a time to a good relational design, and see what it buys you.
In this area, I think, less is quite often more.

Best Wishes,
Chris Travers