[Dbix-class] Relationship question/database structure
David Ihnen
davidihnen at gmail.com
Mon Jan 10 18:54:17 GMT 2011
Yes. That was what i was typing basically when Mr Kinyon's message came
through. Quite right, sir, this is the sort of approach I would take as
well. Each column only has one type of data in it. There are more tables
for cross reference, but thats the hit you take for not putting multiple key
columns in the notes table or as an alternative creating separate notes
tables for each type.
David
On Mon, Jan 10, 2011 at 1:50 PM, Rob Kinyon <rob.kinyon at gmail.com> wrote:
> 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 curre=
nt
> > 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 transacti=
on
> >> note identifier is a DIFFERENT type than a customer service event
> identifier
> >> - but they're in the *same column*. *hits the penalty buzzer* bad db=
a.
> ;)
> >> 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 acce=
ss
> it.
> >>
> >> But relationships don't join on multiple columns.
> >>
> >> As I said, you CAN create multiple belongs-to relationships, and as lo=
ng
> >> 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 wan=
t.
> >> Something like:
> >>
> >> $caseresultset->search( { 'notes.TableName' =3D> 'cases' }, { join =3D>
> 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 =3D> 'case' } );
> >> }
> >>
> >> And query through the chain to find notes for the case.
> >>
> >> my $cases =3D ...ResultSet('cases')->search( { case_id =3D> 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 instanc=
es
> >> of note data, and each type of note data will have its *own* class - i=
ts
> 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 outsi=
de
> the
> >> purveyance of sql::abstract to my understanding of the system.
> >>
> >> Whatever way you manage to munge DBIx::Class into handling this abstra=
ct
> >> data store for note instance data i don't think its going to be as cle=
an
> 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 solution=
s.
> >>
> >> 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 t=
he
> >> 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 a=
nd
> >> > 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
>
> _______________________________________________
> 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
>
-- =
David Ihnen
Voice contact (562) 743-1807
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.scsys.co.uk/pipermail/dbix-class/attachments/20110110/375=
c3921/attachment-0001.htm
More information about the DBIx-Class
mailing list