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 <st...@matsch.com
<mailto:st...@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 <sky...@gmail.com
<mailto:sky...@gmail.com> <mailto:sky...@gmail.com
<mailto:sky...@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" <st...@matsch.com
<mailto:st...@matsch.com>
<mailto:st...@matsch.com <mailto:st...@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
_______________________________________________
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
