[Dbix-class] Newbie - database structure

[Wade.Stuart at fallon.com]

"Zbigniew Lukasiak" <zzbbyy at gmail.com> wrote on 02/22/2008 09:38:07 AM:

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

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