[Dbix-class] Relationship question/database structure

[David Ihnen]

A relational database cannot easy express foreign key constraints to
multiple tables from a single column.  In fact I am sure you are using the
artifact of identical underlying native types to overload a single column to
contain multiple different types of data.  That is to say that a reference
to a transaction entry is not the same as a reference to a customer service
event.  But here you have placed both types of data in the same column,
permitted only because their underlying data types were identical and you
are not making the db check foreign constraints.  Now you want to express
this sloppy schema in the class structure and run into the fact it is going
to be sloppy there too.

You really should fix the schema to be tight and checked but this is not an
ideal world.

There isn't anything stopping you from adding multiple belongs to directives
to your notes result class other than it implies that all the notes are
related to all the tables - semantically absurd but as long as you use the
proper constraints when joining the result will be what you want.  It's
just... sloppy.

No offense intended and I do understand you are working under constraints I
am not familiar with that may limit your solutions.

Trying to be helpful,

Daivd

On Mon, Jan 10, 2011 at 12:58 PM, Skylos <skylos at gmail.com> wrote:

> A relational database cannot easy express foreign key constraints to
> multiple tables from a single column.  In fact I am sure you are using the
> artifact of identical underlying native types to overload a single column=
 to
> contain multiple different types of data.  That is to say that a reference
> to a transaction entry is not the same as a reference to a customer servi=
ce
> event.  But here you have placed both types of data in the same column,
> permitted only because their underlying data types were identical and you
> are not making the db check foreign constraints.  Now you want to express
> this sloppy schema in the class structure and run into the fact it is goi=
ng
> to be sloppy there too.
>
> You really should fix the schema to be tight and checked but this is not =
an
> ideal world.
>
> There isn't anything stopping you from adding multiple belongs to
> directives to your notes result class other than it implies that all the
> notes are related to all the tables - semantically absurd but as long as =
you
> use the proper constraints when joining the result will be what you want.
> It's just... sloppy.
>
> No offense intended and I do understand you are working under constraints=
 I
> am not familiar with that may limit your solutions.
>
> Trying to be helpful,
>
> Skylos
> On Jan 10, 2011 11:59 AM, "Steve" <steve at matsch.com> wrote:
> > Hi All,
> >
> > I'm trying to use an existing schema wherein I have a generic table
> > called 'Note' containing - you guessed it, notes!. The thing is that
> > these notes may be associated with many other types of data and
> > therefore tables in my database. They may be related to a request,
> > user, device, etc. I am sure that this is a fairly common use case, but
> > don't see much in the docs or on the list.
> >
> > The question is two-fold: What's the best structure, and how do we go
> > about defining the DBIC rels?
> > Thanks,
> > Steve
> >
> > _______________________________________________
> > List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
> > IRC: irc.perl.org#dbix-class
> > SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
> > Searchable Archive:
> http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk
>



-- =

David Ihnen
Voice contact (562) 743-1807
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.scsys.co.uk/pipermail/dbix-class/attachments/20110110/d50=
f1a07/attachment.htm