[Dbix-class] Newbie - database structure

[Jess Robinson]

On Wed, 27 Feb 2008, Dermot wrote:

> On 22/02/2008, Zbigniew Lukasiak <zzbbyy at gmail.com> wrote:
>>
>> 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.
>
>
> Is this "joining table" different from a SQL VIEW?

Err, no, not in the slightest, how did you get that impression? A view is 
just a query represented like a table so you dont have to repeat writing 
the query all the time (plus some bonus of the database knowing it up 
front so it can optimise.)

Can you describe how you came to this question? I'm really confused as to 
how one can confuse "joining table" with "view".

Jess