[Dbix-class] Relationship question/database structure

David Ihnen davidihnen at gmail.com
Mon Jan 10 18:34:24 GMT 2011 

You're not thinking specifically enough.  The column which has the ID in it
is being used to store disparate types of information - a transaction note
identifier is a DIFFERENT type than a customer service event identifier -
but they're in the *same column*.  *hits the penalty buzzer*  bad dba.  ;)
 The DB is almost assuredly not constraint checking this column to make sure
that the proper record occurs in the proper foreign table.  *buzz*   This is
sloppy db design and is going to result in compromises in how you access it.

But relationships don't join on multiple columns.

As I said, you CAN create multiple belongs-to relationships, and as long as
your tableName column is included in your query (which is sloppy because it
should be encapsulated) then you're going to get the result you want.
Something like:

$caseresultset->search( { 'notes.TableName' =3D> 'cases' }, { join =3D> not=
es }
);

Also, you can chain resultsets - using a technique to create something like
this in your notes class

sub case_notes {
  return shift->search( { TableName =3D> 'case' } );
}

And query through the chain to find notes for the case.

my $cases =3D ...ResultSet('cases')->search( { case_id =3D> 5 }
)->notes->case_notes;

(which is sloppy because it should be encapsulated in the relationship
without you being explicit about it)

What you really have constructed is an abstract data store for instances of
note data, and each type of note data will have its *own* class - its not
immediately obvious to me if you can actually express that in DBIx::class -
I'm sure that eventually we will be able to do so.  Its complicated though,
and involves the idea of constraints on subqueries which is just outside the
purveyance of sql::abstract to my understanding of the system.

Whatever way you manage to munge DBIx::Class into handling this abstract
data store for note instance data i don't think its going to be as clean as
anybody would like, probably a little sloppy and/or ugly.  Its not the level
of problem the object relational mapper was designed to solve, though i'm
sure it will eventually be capable.  Patch?  ;)  (okay, that would probably
require a pretty significant level of changes...)

David



On Mon, Jan 10, 2011 at 1:18 PM, Steve <steve at matsch.com> wrote:

> I was unclear... sorry for that.  The current 'note' table has two column=
s:
> 'TableName' and 'TableId', where the 'TableName' field is an abbreviation
> for the name of the table the note is related to, and the 'TableId' is the
> record within that table.
>
> So, it is in fact a multi-column join between the note table and several
> other tables, each with their own 'TableName'.
>
> Steve
>
>
> On 1/10/2011 1:07 PM, David Ihnen 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 t=
he
>> artifact of identical underlying native types to overload a single colum=
n to
>> contain multiple different types of data.  That is to say that a referen=
ce
>> to a transaction entry is not the same as a reference to a customer serv=
ice
>> 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 go=
ing
>> 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 <mailto:
>> 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 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,
>>
>>    Skylos
>>
>>    On Jan 10, 2011 11:59 AM, "Steve" <steve at matsch.com
>>    <mailto: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 <http://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
>>
>>
>> _______________________________________________
>> 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
>>
>
> _______________________________________________
> 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/bc7=
52241/attachment-0001.htm

More information about the DBIx-Class mailing list