[Dbix-class] Newbie - database structure

[Dermot]

On 22/02/2008, Zbigniew Lukasiak <zzbbyy at gmail.com> wrote:
>
>
> > >
> > > 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.


That explanation makes more sense to me. I will point out, and I hope I
don't sound pedantic, that the example in the tutorial doesn't have M:M
relationships. the book_authors table has just 2 fields.

    INSERT INTO books VALUES (1, 'CCSP SNRS Exam Certification Guide', 5);
    INSERT INTO books VALUES (2, 'TCP/IP Illustrated, Volume 1', 5);
...snip
    INSERT INTO authors VALUES (7, 'Nathan', 'Torkington');
    INSERT INTO authors VALUES (8, 'Jeffrey', 'Zeldman');
    INSERT INTO book_authors VALUES (1, 1);
    INSERT INTO book_authors VALUES (1, 2);
    INSERT INTO book_authors VALUES (1, 3);
    INSERT INTO book_authors VALUES (2, 4);
    INSERT INTO book_authors VALUES (3, 5);
    INSERT INTO book_authors VALUES (4, 6);
    INSERT INTO book_authors VALUES (4, 7);
    INSERT INTO book_authors VALUES (5, 8);


Thanx
Dp.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.scsys.co.uk/pipermail/dbix-class/attachments/20080222/ec2=
5de30/attachment.htm