[Dbix-class] Relationship question/database structure
Steve
steve at matsch.com
Mon Jan 10 18:47:45 GMT 2011
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 <steve at matsch.com
> <mailto:steve at matsch.com>> 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> <mailto: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>
> <mailto: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> <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 <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
>
>
> _______________________________________________
> 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
More information about the DBIx-Class
mailing list