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 <[email protected] >> <mailto:[email protected]>> 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" <[email protected] >> <mailto:[email protected]>> 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/[email protected] >> >> >> >> >> -- >> 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/[email protected] > > _______________________________________________ > 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/[email protected] -- Ben Hitz Senior Scientific Programmer ** Saccharomyces Genome Database ** GO Consortium Stanford University ** [email protected] _______________________________________________ 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/[email protected]
