Trouble with keys and relationships

[Dubya]

Hi,

I'm totally new to MySQL and also to SQLyog (only experience with relational database is in… MS Access).

For a book publisher's project I'm trying to build my (very first) MySQL database and I'm stuck with the foreign key/relationship aspect.

I have a table with books (id, title, subtitle, pages, etc…) and a table with authors (id, author_name, author_address, etc…).

Now, my idea was to use the book table as parent table and authors table as child (from what I've read from tutorials and manual, this seemed OK to me).

But my colleague from the IT-department suggest that I'd use a THIRD table (table_books_authors), with an auto-increment Id as the primary key and the book_id and category_id as foreign keys). This colleague is an experienced database-guy (works mainly with SQLServer and also with Oracle) and I'm no expert in this field.

My questions:

– what's the right approach in a MySQL environment (MySQL is also new for my colleague)

– how do I get this thing done in SQLyog

– any useful suggestions are welcomed!

Thanks for your time.

Dubya

ps: of course I have more than 2 tables I've mentioned. But if someone can explain me the principle of this technique I'll (probably) be able to figure it out for my other tables that are quite similar to the 2 mentioned above.

[Shadow]

Your IT friend was right suggesting to use the third table because books and authors have a many-to-many relationship (a book may have several authors and an author may write several books) and relational dbs do not really like such connections. To resolve such situations (they are quite common) a third table is introduced that hold the primary keys of both tables, thus holding every possible combination of the two primary keys. Actually, I don't understand why you use an autoincrement id column in table_books_authors table, book_id and author_id fields combined may serve as primary key.

How to do it in SQLyog? Just create the table_books_authors table with two columns (book_id, author_id) and mark both columns as primary key.

As you are new to MySql, I would like to point out that MySql is able to use different storage engines for the tables. The default is MyISAM which – currently – cannot handle relationships. If you want to to use relationships then INNODB storage engine should be used.

[Author]

Posts