[Dbix-class] Relationship question/database structure

Benjamin Hitz hitz at genome.stanford.edu
Mon Jan 10 19:40:27 GMT 2011


At the risk of exposing myself to ridicule...   I have such a similar "Note" table in our schema.  It's not my fault, I inherited it.
You can munge DBIx::Class to follow such a non-relational link.  Of course you cannot autogenerate the rels with DBIx::Class::Schema::Loader,


David wrote
>But relationships don't join on multiple columns.

Yeah, I somehow missed this in the docs, but you can kludge it by defining a method in your ResultSource/ResultSet classes.
We handle the DB integrity via triggers (Oracle)

I don't recommend this.  I campaign yearly to eradicate this from our database (we actually 2 or 3 non-relational bits like this), but as of yet I have been unsuccessful.

Ben



On Jan 10, 2011, at 10:18 AM, Steve wrote:

> I was unclear... sorry for that.  The current 'note' table has two columns: '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 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 <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

--
Ben Hitz 
Senior Scientific Programmer ** Saccharomyces Genome Database ** GO Consortium
Stanford University ** hitz at genome.stanford.edu






More information about the DBIx-Class mailing list