[Dbix-class] has_one joining through another table

[Marc Mims]

* Steve Francia <steve at takkle.com> [070626 16:00]:
> 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.

At least in the problem you presented, I don't see the data duplication
problem with a foreign key to the user table in the picture table.  The
key itself would exist in both the picture table and the user_picture
table, but I think that is a natural consequence of the relationships.

I've run into this very situation in some legacy code I worked on
recently.  The database had company table, a contact table, and a
company_contact linking table with a primary_contact flag.  The code to
change the primary contact was convoluted and in the application it was
easy to end up with multiple primary contacts or no primary contact at
all.  By replacing the primary_contact flag in the linking table with a
foreign key in the company table, the code to change primary contacts
was trivial.  Enforcing one-and-only-one primary in the database was
easy and required no extra application code.

> 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?
 
I don't know anything about the specifics of your application.  If you
have multiple owners, the best solution might be the owner flag in the
user_picture table.  Or, it might be a separate owner_picture table.
The latter would be applicable if you could have an *owners* of pictures
that are not necessarily *users* of those pictures.

> 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.

Can you be more specific about the more complicated example?  Initially,
it sounds like foreign key for and 2 linking tables might be the answer.

	-Marc