The canonical approach is: orders line_items persons
notes orders_x_notes line_items_x_notes persons_x_notes So, each type has an xref table to the notes table. But, there's only one notes table. Rob On Mon, Jan 10, 2011 at 13:47, Steve <[email protected]> wrote: > Reading between the lines, is it your opinion that having several > differently named tables with identical fields is better than the current > approach? > On 1/10/2011 1:34 PM, David Ihnen wrote: >> >> 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' => 'cases' }, { join => notes >> } ); >> >> Also, you can chain resultsets - using a technique to create something >> like this in your notes class >> >> sub case_notes { >> return shift->search( { TableName => 'case' } ); >> } >> >> And query through the chain to find notes for the case. >> >> my $cases = ...ResultSet('cases')->search( { case_id => 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 <[email protected] >> <mailto:[email protected]>> 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]> <mailto:[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]> >> <mailto:[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> <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 <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] >> >> >> _______________________________________________ >> 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] > -- Thanks, Rob Kinyon _______________________________________________ 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]
