[Dbix-class] Newbie - database structure

[Zbigniew Lukasiak]

On Fri, Feb 22, 2008 at 4:26 PM, Dermot <paikkos at googlemail.com> 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.

Cheers,
http://perlalchemy.blogspot.com/