Thread: First-class Polymorphic joins?

It surprises me that no SQL database to my knowledge has polymorphic joins as a first-class feature.

A polymorphic join is where a fk contains not just an id but an indicator of which table it refers to. So you could
havea "tags" table, that can attach tags to any of a variety of other tables. Rails handles this by including the table
nameas a string. 

This seems a reasonable thing to want to do, and it seems that the database could handle it by combining the fields
fromthe target tables in the result. 

I also think migrations ought to be a first-class feature…

On 08/13/2015 05:23 PM, Guyren Howe wrote:
> It surprises me that no SQL database to my knowledge has polymorphic joins as a first-class feature.
>
> A polymorphic join is where a fk contains not just an id but an indicator of which table it refers to.

I am pretty sure it already does that:

http://www.postgresql.org/docs/9.4/interactive/sql-createtable.html

REFERENCES reftable [ ( refcolumn ) ]

  So you could have a "tags" table, that can attach tags to any of a
variety of other tables. Rails handles this by including the table name
as a string.
>
> This seems a reasonable thing to want to do, and it seems that the database could handle it by combining the fields
fromthe target tables in the result. 
>
> I also think migrations ought to be a first-class feature…

So is this the push the ORM into the database day?

>


--
Adrian Klaver
adrian.klaver@aklaver.com

On 08/13/2015 05:59 PM, Guyren Howe wrote:

Ccing list

> On Aug 13, 2015, at 17:49 , Adrian Klaver <adrian.klaver@aklaver.com> wrote:
>>
>>> A polymorphic join is where a fk contains not just an id but an indicator of which table it refers to.
>>
>> I am pretty sure it already does that:
>>
>> http://www.postgresql.org/docs/9.4/interactive/sql-createtable.html
>>
>> REFERENCES reftable [ ( refcolumn ) ]
>
> I apologize for not being clearer.
>
> The point is that the fk in different rows can reference different tables. I might want to be able to attach a tag to
aperson or a blog post, say. And then I want to find all the persons and blog posts with a particular tag, in a single
query.

Could you just not turn that around?:

tag
   tag_id
   tag_desc

person
   person_id
   tag_fk references tag

blog
   blog_id
   tag_fk references tag

>
> The simplest implementation is to have a table reference as a first-class value I can store in a field.
>


--
Adrian Klaver
adrian.klaver@aklaver.com

El 13/08/15 a las 21:23, Guyren Howe escribió:
>
> I also think migrations ought to be a first-class feature…

What do you mean with "migrations ought to be a first-class feature"?

There have been, and there still are efforts for making upgrading as
smooth and simple as possible, but I'm not really sure where you want to
get with this.

Cheers,

--
Martín Marqués                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

On 08/13/2015 05:37 PM, Martín Marqués wrote:
> El 13/08/15 a las 21:23, Guyren Howe escribió:
>>
>> I also think migrations ought to be a first-class feature…
>
> What do you mean with "migrations ought to be a first-class feature"?
>
> There have been, and there still are efforts for making upgrading as
> smooth and simple as possible, but I'm not really sure where you want to
> get with this.

I think Guyren is talking about something like Django or Rails
migrations, Alembic, Sqitch, etc. A way to do changes to database
objects either whole or as incremental changes. Basically a schema
versioning method.

>
> Cheers,
>


--
Adrian Klaver
adrian.klaver@aklaver.com

El 13/08/15 a las 23:17, Adrian Klaver escribió:
> On 08/13/2015 05:37 PM, Martín Marqués wrote:
>> El 13/08/15 a las 21:23, Guyren Howe escribió:
>>>
>>> I also think migrations ought to be a first-class feature…
>>
>> What do you mean with "migrations ought to be a first-class feature"?
>>
>> There have been, and there still are efforts for making upgrading as
>> smooth and simple as possible, but I'm not really sure where you want to
>> get with this.
>
> I think Guyren is talking about something like Django or Rails
> migrations, Alembic, Sqitch, etc. A way to do changes to database
> objects either whole or as incremental changes. Basically a schema
> versioning method.

OK, I misunderstood the statement.

In that case, he should take a look at sqitch. Very, very nice IMO.


--
Martín Marqués                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services