Yes. That was what i was typing basically when Mr Kinyon's message came through. Quite right, sir, this is the sort of approach I would take as well. Each column only has one type of data in it. There are more tables for cross reference, but thats the hit you take for not putting multiple key columns in the notes table or as an alternative creating separate notes tables for each type.
David On Mon, Jan 10, 2011 at 1:50 PM, Rob Kinyon <[email protected]> wrote: > 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] > -- 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]
