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]
