cayenne-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Aristedes Maniatis <>
Subject Re: AW: Mapping a relationship with type List<Long>
Date Wed, 13 Jun 2012 07:04:54 GMT
On 13/06/12 12:53am, Schönfisch, Jörg wrote:
> Thanks for your detailed answer!
> I think what I'm trying to achieve is a bit more difficult than this. I'm already using
polymorphic joins and it works really well. However, in the scenario I try to model here I
don't want to use single table inheritance for performance reasons.

What performance problem do you see? We have tables with several hundred thousand records
managed in this way without the slightest issue. And Cayenne maps queries to proper SQL with
joins which the db optimises nicely.

> I'm implementing some kind of history for my entities. If a relation between two entities
is changed, the old relation is written to a history table and then gets replaced by the new
one. The historized relation still references the two entities in the normal tables. If now
one of these entities is deleted, it is also put in a separate history table. The historized
relation from before now references one normal entity and one historized entity. So what is
referenced by the relation changed without this information being explicitly available anywhere.
> The IDs for all these entities and relations are permanent, so the normal and the corresponding
historized entities always have the same ID and only differ by their revision (and the changed
values, of course). The normal and the history tables should be split to improve query performance
if the information from the history is not needed.

That just made my head hurt. I can see what you are doing... but it is complicated. Are you
sure you need to save this to a new table? Will it really be a performance problem or are
you just assuming it will be? More records in a table need not slow things down if the indexes
work well for you.

It sounds like you have two parallel schemas. One for current data and another for all the

> It might be possible to model this as vertical inheritance with NormalEntity and HistorizedEntity
being subclasses to a common superclass. But this would complicate my model and introduce
additional tables which is what I am trying to avoid.

But above you said that "it is also put in a separate history table". I'm unclear whether
you have separate database tables or not. At any rate, whether you have separate database
tables, it sounds like a Java subclass approach will give you some benefits.


> So I want to select only the IDs from the relationship table and then do two additional
queries for the normal and the historized entities identified by these. But I have not found
a possible to model this.
> Cheers,
> Joerg
> -----Ursprüngliche Nachricht-----
> Von: Aristedes Maniatis []
> Gesendet: Dienstag, 12. Juni 2012 15:53
> An:
> Betreff: Re: Mapping a relationship with type List<Long>
> On 12/06/12 7:01pm, Schönfisch, Jörg wrote:
>> Hi,
>> I have two tables, one relates to an object entity, the other is a join table. A
FK relationship exists between those two. However the "other" IDs in the join table may be
present in several different tables so I cannot define a FK relationship for them. Thus I
directly want to read a list of IDs and not convert them into object entities. The mapping
of IDs to objects will later take place in the business code.
>> Any hints on how to do this or if it's possible at all?
> What you are describing is a polymorphic join. In Rails it looks like this:
> There is no built-in construction in Cayenne, but it isn't hard to map. Let's pretend
you want to have the ability to add "attachments" to various other entities. Contacts, Artists,
Paintings. These attachments might be a picture or other information.
> So you create:
> [Attachment]
> id
> other columns
> [AttachmentRelation]
> foreignRecordId (int)
> foreignTable (string or enum)
> [ContactAttachmentRelation]
> [PaintingAttachmentRelation]
> [ArtistAttachmentRelation]
> These last three classes you create in Cayenne model and you make them a subclass of
AttachmentRelation. There is no database table that goes with them, so effectively you are
creating them as single table inheritance. Three subclasses, and only table table in the database.
> Put a method like this in the AttachmentRelation:
>      public abstract void setAttachedRelation(Attachable attachable);
> And then code like this goes in the ContactAttachmentRelation subclass:
>     public void setAttachedRelation(Attachable attachable) {
>       setAttachedContact((Contact) attachable);
>     }
> The model will have a qualifier like this:
>     <obj-entity name="ContactAttachmentRelation" superEntityName="AttachmentRelation"
className="x.y.z.ContactAttachmentRelation" clientClassName="x.y.z.ContactAttachmentRelation">
>       <qualifier><![CDATA[entityIdentifier like "Contact"]]></qualifier>
>     </obj-entity>
> There is a bit more to do, but it is pretty simple. The main trick is to use the Cayenne
inheritance and let it handle all your SQL generation. It works really nicely and we've had
something like this in production for many years.
> Ari

Aristedes Maniatis
GPG fingerprint CBFB 84B4 738D 4E87 5E5C  5EFA EF6A 7D2E 3E49 102A

View raw message