[Dbix-class] Relationship question/database structure
Rob Kinyon
rob.kinyon at gmail.com
Mon Jan 10 18:50:24 GMT 2011
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 <steve at matsch.com> 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 <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
>
> _______________________________________________
> 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
>
--
Thanks,
Rob Kinyon
More information about the DBIx-Class
mailing list