[Dbix-class] Relationship question/database structure

[Steve]

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>> 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>> 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/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