"Zbigniew Lukasiak" <[EMAIL PROTECTED]> wrote on 02/22/2008 09:38:07 AM:
> On Fri, Feb 22, 2008 at 4:26 PM, Dermot <[EMAIL PROTECTED]> wrote: > > > > > >There is a section about making 3 tables with SQLite3; books, authors and > > > book_authors. The last table is described as > > > >'book_authors' is a many-to-many join table between books & authors' > > > > > > > >My query is: Is this the "best practise" or an over-simplification for us > > > newbies? Would you normally create a table for >joins to show many to many > > > relationships? I would have though that you could use SQL statements to > > > retreive that rather >than create a table of it. > > > > > > > > > To answer your question, yes, you need a join table. Each record in it > > > expresses a relationship between a record in table A and a record in table > > > B. > > > > > > I'd recommend working through an SQL book that explains SQL concepts in > > more > > > detail as that really is a prerequisite for using a database or DBIC > > > effectively. > > > E.g. http://tinyurl.com/ywdxfu > > > > > > > > I am unsettled by this. I went and re-read chapter 4 of the book I do have > > (learning MYSQL) on database design and modelling. I still can't see the > > value in creating a separate table for a join. > > > > If you have a 1:N table of authors and a table of books with the author ID > > as a foreign key, surely that all you need to create any join you might > > want. I know sqlite3 doesn't understand foreign keys but DBIC can create > > them for you. Isn't that where the relationship is established? > > The standard technique with a joining table is for the case when you > can have multiple authors on the book (hence the name many to many) - > you cannot store all of them in one field. If all you need is a 1:N > relationship then you are ok without the linking table. To really beat it in Dermot, you seem to be mistaking the "best practice" for 1:N (has many) with what we are talking about here: N:N (many to many) where each side of the relation can have bi- directional links to 0->N items. Table Author ID Author 0 Wade 1 Zbigniew 2 Dermot Join table: Author Book 0 0 0 1 0 2 1 0 1 2 2 1 Book table: ID Book 0 foo 1 bar 2 bas There are ways (and some reasons to do so for optimization) to do this without a join table, but 99 times out of 100 this is the best bet. -Wade _______________________________________________ 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]
