FAQ
Hi,

Is it possible to map fake foreign keys with CM?

In many DBs for webapps there seem to be quite a few tables (for entities like Tags, Comments, Attachments, Events) to have relationships to many other tables, and instead of a FK, a combination of two fields is used (target_table_name and target_row_id).

I know that other ORMs (like ActiveRecord from RoR) have this feature, but I'm not sure about Cayenne.

Thanks in advance,

George.

Search Discussions

  • Andrus Adamchik at Jan 10, 2010 at 2:36 pm
    Not directly, but you can emulate that with entity qualifiers by
    filtering the generic entity on the value of the column that is not
    participating in a join. You can even map that as an inheritance
    hierarchy.

    Andrus

    On Jan 10, 2010, at 4:05 PM, George Stan wrote:
    Hi,

    Is it possible to map fake foreign keys with CM?

    In many DBs for webapps there seem to be quite a few tables (for
    entities like Tags, Comments, Attachments, Events) to have
    relationships to many other tables, and instead of a FK, a
    combination of two fields is used (target_table_name and
    target_row_id).

    I know that other ORMs (like ActiveRecord from RoR) have this
    feature, but I'm not sure about Cayenne.

    Thanks in advance,

    George.
  • Aristedes Maniatis at Jan 10, 2010 at 10:21 pm
    Having actually done this the first way Andrus mentions, my advice is to do it the second way (as an inheritance) if you can. The first way is pretty easy, but you don't end up with actual SQL JOIN statements when (say) you try to find all records Tagged with a certain tag. You end up with very long SELECT IN.... across the join. Which works fine until you try to join 10,000 records.

    George, could you point us to the documentation for how ActiveRecord implements this? I'd be interested in their approach.

    Ari

    On 11/01/10 1:35 AM, Andrus Adamchik wrote:
    Not directly, but you can emulate that with entity qualifiers by
    filtering the generic entity on the value of the column that is not
    participating in a join. You can even map that as an inheritance hierarchy.

    Andrus

    On Jan 10, 2010, at 4:05 PM, George Stan wrote:
    Hi,

    Is it possible to map fake foreign keys with CM?

    In many DBs for webapps there seem to be quite a few tables (for
    entities like Tags, Comments, Attachments, Events) to have
    relationships to many other tables, and instead of a FK, a combination
    of two fields is used (target_table_name and target_row_id).

    I know that other ORMs (like ActiveRecord from RoR) have this feature,
    but I'm not sure about Cayenne.

    Thanks in advance,

    George.
    --
    -------------------------->
    Aristedes Maniatis
    GPG fingerprint CBFB 84B4 738D 4E87 5E5C 5EFA EF6A 7D2E 3E49 102A
  • Kevin Menard at Jan 11, 2010 at 1:09 am
    This actually something I've been meaning to add in to the DB importer. I
    have a few Rails apps I'd like to try to get going with Cayenne.

    ActiveRecord kills me in that it doesn't create FKs at all. The idea is
    that AR maintains the relationships at all times and it may actually use
    several queries to do what could otherwise be done in one. So, in AR, the
    most common relationship is just a column of type "other_table_name_id" and
    then AR just knows to join using that column. It is customizable, but in
    practice it doesn't happen that often.

    More complicated would be AR's notion of polymorphic relationships, which I
    guess were added as a way to avoid join tables.

    Suffice it to say, I've had many arguments with people over the design of
    AR. I usually point to Cayenne as an example of an ORM that can work with
    FKs just fine. Of course, many in the Ruby community are not familiar with
    Cayenne, so it tends not to be the best example.

    --
    Kevin

    On Sun, Jan 10, 2010 at 5:21 PM, Aristedes Maniatis wrote:

    Having actually done this the first way Andrus mentions, my advice is to do
    it the second way (as an inheritance) if you can. The first way is pretty
    easy, but you don't end up with actual SQL JOIN statements when (say) you
    try to find all records Tagged with a certain tag. You end up with very long
    SELECT IN.... across the join. Which works fine until you try to join 10,000
    records.

    George, could you point us to the documentation for how ActiveRecord
    implements this? I'd be interested in their approach.

    Ari


    On 11/01/10 1:35 AM, Andrus Adamchik wrote:

    Not directly, but you can emulate that with entity qualifiers by
    filtering the generic entity on the value of the column that is not
    participating in a join. You can even map that as an inheritance
    hierarchy.

    Andrus

    On Jan 10, 2010, at 4:05 PM, George Stan wrote:

    Hi,

    Is it possible to map fake foreign keys with CM?

    In many DBs for webapps there seem to be quite a few tables (for
    entities like Tags, Comments, Attachments, Events) to have
    relationships to many other tables, and instead of a FK, a combination
    of two fields is used (target_table_name and target_row_id).

    I know that other ORMs (like ActiveRecord from RoR) have this feature,
    but I'm not sure about Cayenne.

    Thanks in advance,

    George.
    --
    -------------------------->
    Aristedes Maniatis
    GPG fingerprint CBFB 84B4 738D 4E87 5E5C 5EFA EF6A 7D2E 3E49 102A
  • Aristedes Maniatis at Jan 11, 2010 at 1:22 am

    On 11/01/10 12:08 PM, Kevin Menard wrote:
    More complicated would be AR's notion of polymorphic relationships, which I
    guess were added as a way to avoid join tables.
    My coincidence Lachlan Deck and I were discussing this just this morning. We have to think about what is involved, but what about an interface you can define in CM on which you can then perform a Query? Then rather than forcing inheritance on users, you could just define an interface.

    The common examples (as per the original poster) are attachments, comments and tags. So you want to be able to define a Taggable interface implemented by Artists, Paintings, etc. That then ties back to Tags and a special many-many Taggable_Tags join table with "foreign_key" and "foreign_table" fields. This implementation looks a lot like vertical inheritance, but without the inheritance.

    Do you think this is achievable? Andrus, I know you've looked at vertical inheritance... do you think this would be a natural extension of that work, or quite different?

    Ari

    --
    -------------------------->
    Aristedes Maniatis
    GPG fingerprint CBFB 84B4 738D 4E87 5E5C 5EFA EF6A 7D2E 3E49 102A
  • Kevin Menard at Jan 11, 2010 at 1:47 am

    On Sun, Jan 10, 2010 at 8:21 PM, Aristedes Maniatis wrote:
    On 11/01/10 12:08 PM, Kevin Menard wrote:

    More complicated would be AR's notion of polymorphic relationships, which
    I
    guess were added as a way to avoid join tables.
    My coincidence Lachlan Deck and I were discussing this just this morning.
    We have to think about what is involved, but what about an interface you can
    define in CM on which you can then perform a Query? Then rather than forcing
    inheritance on users, you could just define an interface.

    The common examples (as per the original poster) are attachments, comments
    and tags. So you want to be able to define a Taggable interface implemented
    by Artists, Paintings, etc. That then ties back to Tags and a special
    many-many Taggable_Tags join table with "foreign_key" and "foreign_table"
    fields. This implementation looks a lot like vertical inheritance, but
    without the inheritance.
    So, the problem I've always had with this approach is that renaming a table
    invalidates your data. Additionally, you're always doing a join on a
    varchar field for which an index may not buy you a whole lot (best case is a
    1/n partitioning). The benefit is you don't need n join tables.

    --
    Kevin
  • Aristedes Maniatis at Jan 11, 2010 at 3:56 am

    On 11/01/10 12:46 PM, Kevin Menard wrote:
    On Sun, Jan 10, 2010 at 8:21 PM, Aristedes Maniatiswrote:
    On 11/01/10 12:08 PM, Kevin Menard wrote:

    More complicated would be AR's notion of polymorphic relationships, which
    I
    guess were added as a way to avoid join tables.
    My coincidence Lachlan Deck and I were discussing this just this morning.
    We have to think about what is involved, but what about an interface you can
    define in CM on which you can then perform a Query? Then rather than forcing
    inheritance on users, you could just define an interface.

    The common examples (as per the original poster) are attachments, comments
    and tags. So you want to be able to define a Taggable interface implemented
    by Artists, Paintings, etc. That then ties back to Tags and a special
    many-many Taggable_Tags join table with "foreign_key" and "foreign_table"
    fields. This implementation looks a lot like vertical inheritance, but
    without the inheritance.
    So, the problem I've always had with this approach is that renaming a table
    invalidates your data. Additionally, you're always doing a join on a
    varchar field for which an index may not buy you a whole lot (best case is a
    1/n partitioning). The benefit is you don't need n join tables.
    There is no reason why the descriminator (class designator) couldn't be a java enum mapped to int or anything else that made the database fast. Even if was text as far as Java was concerned, mysql has the ability to define an enum which makes it smaller/faster. [1] The very same problem exists for vertical and horizontal inheritance.


    Ari

    [1] http://dev.mysql.com/doc/refman/5.0/en/enum.html


    --
    -------------------------->
    Aristedes Maniatis
    GPG fingerprint CBFB 84B4 738D 4E87 5E5C 5EFA EF6A 7D2E 3E49 102A
  • Aristedes Maniatis at Jan 11, 2010 at 7:57 am

    On 11/01/10 12:21 PM, Aristedes Maniatis wrote:
    Do you think this is achievable? Andrus, I know you've looked at
    vertical inheritance... do you think this would be a natural extension
    of that work, or quite different?
    Thinking about this a bit more (it actually solves a problem I have right now): maybe my introduction of the interface idea is a red-herring. That could be useful, but isn't really the core of the problem. Instead, what we need are qualifiers on db_Relationships. We already have a very similar idea in inheritance.

    Let me know if you want me to flesh out the idea in more detail, or if it is obvious from what I've mentioned so far how it might work.


    Ari

    --
    -------------------------->
    Aristedes Maniatis
    GPG fingerprint CBFB 84B4 738D 4E87 5E5C 5EFA EF6A 7D2E 3E49 102A
  • Zissis Trabaris at Jan 13, 2010 at 1:36 am
    Qualifiers on dbRelationships would be really nice. The only way to do
    this right now is to overload the assessor methods. It would be really
    nice if it where modeled in and if the add methods would run the
    qualifier and throw an exception if the added object entity did not
    comply.

    Zissis Trabaris * Chief Technology Officer * INSYSWARE * 3235 West River
    Road, Grand Island, New York, 14072, USA
    Mobile (716) 930-5654 * Office (518) 636-4118 * Fax (716) 625-1305 *
    z.trabaris@insysware.com * www.insysware.com

    CONFIDENTIALITY: This email (including any attachments) may contain
    confidential, proprietary and privileged information, and unauthorized
    disclosure or use is prohibited. If you received this email in error,
    please notify the sender and delete this email from your system. Thank
    you.

    -----Original Message-----
    From: Aristedes Maniatis
    Sent: Monday, January 11, 2010 2:57 AM
    To: dev@cayenne.apache.org
    Subject: Re: Mapping fake FKs as relationships?
    On 11/01/10 12:21 PM, Aristedes Maniatis wrote:
    Do you think this is achievable? Andrus, I know you've looked at
    vertical inheritance... do you think this would be a natural extension
    of that work, or quite different?
    Thinking about this a bit more (it actually solves a problem I have
    right now): maybe my introduction of the interface idea is a
    red-herring. That could be useful, but isn't really the core of the
    problem. Instead, what we need are qualifiers on db_Relationships. We
    already have a very similar idea in inheritance.

    Let me know if you want me to flesh out the idea in more detail, or if
    it is obvious from what I've mentioned so far how it might work.


    Ari

    --
    -------------------------->
    Aristedes Maniatis
    GPG fingerprint CBFB 84B4 738D 4E87 5E5C 5EFA EF6A 7D2E 3E49 102A
  • George Stan at Jan 11, 2010 at 3:28 pm
    Thank you all for your advice.
    Having actually done this the first
    way Andrus mentions, my advice is to do it the second way
    (as an inheritance) if you can.
    Hmm, I'm trying, but CM seems to allow me only one element for the inheritance :(. Unfortunately, many Entities must inherit from more than one since they have Tags, Comments and Events too:(.

    George, could you point us to the documentation for how
    ActiveRecord implements this?
    I'm afraid there's no such documentation, as this is a core Rails feature,
    there are not too many architecture documents about them - just the sources :(.

    George.
  • Aristedes Maniatis at Jan 21, 2010 at 9:18 am

    On 11/01/10 1:05 AM, George Stan wrote:
    Hi,

    Is it possible to map fake foreign keys with CM?

    In many DBs for webapps there seem to be quite a few tables (for entities like Tags, Comments, Attachments, Events) to have relationships to many other tables, and instead of a FK, a combination of two fields is used (target_table_name and target_row_id).

    I know that other ORMs (like ActiveRecord from RoR) have this feature, but I'm not sure about Cayenne.

    Hi George,

    There is another option which I should have pointed out. Say you have an Attachments table. And an AttachmentsRelation table which is the many many join. Then make AttachmentsRelation a "single table inheritance" entity with children:

    AttachmentsRelation_Contact
    AttachmentsRelation_Room
    AttachmentsRelation_Building
    AttachmentsRelation_Painting

    Then each of these children is mapped with a join in Cayenne modeler to the appropriate Contact, Room, etc entities. There will be a qualifier in the single table inheritance setup which will be something like "contact", "room", etc as needed.

    That really should do everything you wanted.


    Cheers
    Ari



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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
groupdev @
categoriescayenne
postedJan 10, '10 at 2:06p
activeJan 21, '10 at 9:18a
posts11
users5
websitecayenne.apache.org

People

Translate

site design / logo © 2021 Grokbase