[Dbix-class] has_one joining through another table

[Wade.Stuart at fallon.com]

Matt,


> > Sadly, that is the way we had it, we decided to switch to using the
> > owner flag to try to eliminate duplication of data in the database.
>
> False economy - that isn't duplication, as the first reply to the this
> post points out. Won't work the way you've got it, we don't tend to add
> code to make broken database layouts easier to core.

True, but what if you have properties about the relationship that reflect
the many to many map directly? ignore one-owner-per-picture, and suppose
you have a set of data such as preferences that are directly related to the
many-to-many relationship between user and picture.  Is the mapping table
not the correct place for this data? Most of the database schemas I have
seen over the year would place this relationship modifying data in the
user_picture mapping tables.  What is your view on this?  make another
table that stores the preferences?  Am I mistaken that this is pretty
"normal" database design?



>
> > This does bring up an interesting question though. If there were
> > multiple owners, but then that would just be a fairly typical
> > many_to_many relationship, right?
>
> Yep.
>
> > Is there a way to do it given the current database layout? It would be
> > good to know from a design standpoint, if such a thing were possible. I

> > have a more complicated example that requires an answer to this simpler

> > one. It is more or less the same exact scenario, except instead of
> > owner, it is type. There are 3 types, which translate to three
different
> > relationships, two are a many_to_many, one is a has_one. I know the
> > database wouldn't enforce that, but our application would.
>
> Again, you probably want to rethink that and model the has_one as a
separate
> table.
>
> Your database is there to enforce the integrity of your data, that's what
> the whole constraint subsystem is designed to do. It might take a bit
> more thinking to get it to work right, but it's always better to let the
> database do what it's good at.

Understood, given a constraint on one owner -- without this constraint what
is best practice for storing tightly coupled many-to-many relationship
data?

>
> If you have problems with query speed then a de-normalized lookup table
> populated from triggers or leaning on memcached will work, but you should
> always avoid sacrificing relational integrity for performance until
you're
> -absolutely- sure you need ti, and the whole point of an ORM layer is
> that you -don't- have to sacrifice convenience for relational integrity
:)

True,  but I think in the case of this tightly coupled data and from what I
have seen normal database layout -- why is this not easy in the ORM layer?


-Wade