Many-to-many relationships and forms

[evil__donkey]

I'm using an Access database that has three tables:

Books (PK: BooksID)
BookAuthor (junction table, Keys: BookID and AuthorID)
Authors (PK: AuthorID)

A book can have multiple authors and an author can write multiple books, so I've added the junction table. Now, I have a form where I can view book records or add new records. The problem is with adding new records. Now, each book can have multiple records, so I'm wondering how I could allow the user to enter new author information while still maintaining the book-author relationship. Any help will be appreciated.

Evil. :-)

[BruceG]

On the form to add a new book, you could have a listbox (or some other mechanism) to show existing authors and allow the user to pick one or more authors from that list, or have an option to add a new author. When you save, you not only insert your new book row, but insert one or more row(s) into the junction table using the newly added book ID and the author ID (be it an existing author or newly added one).

On your form to add a new author, I am not seeing the need to allow adding of book info - in other words, that would be a straight-forward form to just populate the author table.

[jeroen79]

Create another BookAuthor record.

There is a book "VB for noobies".
It is written by "John".
You have "John" in Author, "VB for noobies" in Book and "VB for noobies";"John" in BookAuthor.
You find out that "Mary" also contributed to the book.
You make a new BookAuthor containing "VB for noobies";"Mary".

When you want to know who authored "VB for noobies" you query BookAuthor for all records where .Book is "VB for noobies".
This will turn out "VB for noobies";"John" and "VB for noobies";"Mary".
You then go through all records in the query and read .Author of each record.

You will still have one Book for "VB for noobies" and one Author record each for "John" and "Mary".
You add authors to books and books to author by adding BookAuthor junction records that link to the book and the author.

[RobCrombie]

I studied DB design yeeeeears ago, and I worked in the IT section of a company for 13 years. We had a very large DB which was fully relational (not hierarchical).
I am pretty sure that 'junction tables' were as rare as hen's teeth.

Can't you just have two Tables Books and Authors
And have one to many in both directions ?

I can only recall once, when we were considering a 'Junction Table', and that was to fulfill some special need, which I cannot recall.

[BruceG]

Rob -
I beg to differ, but in a highly normalized database, you are bound to have "junction" tables (or whatever other term you want to use to describe them). I am working on a system now that's got a boatload of them. Basically they define the many-to-many relationship between two otherwise independent tables (in this case Books and Authors).

[RobCrombie]

We rarely went past 'third normal form'

Can you point out what benefit he will gain from this 'Junction Table' ?

can you point out where it 'will bite hiim in the bum' if he doesn't have it ?

Otherwise I say 'Keep It Simple'

[BruceG]

It's not a matter of biting him in the bum - it goes back to the design philosphy of whoever is designing the database. In many cases I agree it is perfectly practical to have some level of de-normalization (performance, keeping things simple, etc.)

The junction table thing is useful for keeping data redundancy to a minimum. In this case, if you are storing a good deal of info about the author, and that author wrote many books, you would have to have that many author records for the same book (i.e. many rows of the author table would be the same except for the BookID field).

I don't think there is a right or wrong answer here - as I said it goes back to design philosphy.

[szlamany]

I've got to say, JUNCTION or BRIDGE tables are rare, but in this case...

You have BOOKS - they obviously belong in a table called BOOKS.

You have AUTHORS - they obviously belong in a table called AUTHORS.

Since the requirement was already stated that a book can have more than one author, how else would you do it without a JUNCTION table called BooksAuthors?

[jeroen79]

Key is that it works.

Without junction tables you can end up storing the same data in multiple places.
If you keep an eye on that in your software then that is OK, if you don't things can get conflicted.

You can also link a book to multiple authors by adding .Book1 to .BookX to the table, but that will make queries more complex and restricts the number of authors per book.

Junction tables allow you to link an infinite number of books to an infinite number of authors.

It also depends on what sort of data is stored.
The number of authors per book tends to be limited.
The number of customers per article can be very large.

[szlamany]

I agree that the JUNCTION table makes the most sense here in this case...

I've got a situation where we have a LEDGER record - keyed by a TRANSACTION ENTRY #, with debit/credit and encumbrance figures. Some ledger records are related to a PO record. So in the LEDGER row we have a PO ENTRY column - 0 if there is no PO row and non-zero to point to the entry # of the PO row if one exists.

Some people would say that a BRIDGE table would make sense here - a table of simply TRANSACTION ENTRY # and PO ENTRY #.

I happen to stop normalizing at this point - I don't mind 50% of my ledger rows having 0 for the PO row. We are forced to have a 0 PO ENTRY for REFERENTIAL INTEGRITY to work...

But we JOIN as:

LEFT JOIN PO_T PO ON PO.POENTRY=LE.POENTRY AND LE.POENTRY<>0

We aren't creating any duplicate data problems and the avoidance of a JUNCTION/BRIDGE table in this case simply seems to simple to ignore...

For what it's worth

[RobCrombie]

I did say it was yeeeeears ago.
And I did say they were as rare as hen's teeth.

Since the requirement was already stated that a book can have more than one author, how else would you do it without a JUNCTION table called BooksAuthors?

Perhaps I'll concede that we have found a hen's tooth.

It's 4:30 in the morning here, so that's my excuse.

[szlamany]

I did say it was yeeeeears ago.
And I did say they were as rare as hen's teeth.

Perhaps I'll concede that we have found a hen's tooth.

It's 4:30 in the morning here, so that's my excuse.

I hate them as well - and also agree it's rare - 4:30 in the morning or not

[RobCrombie]

Thanks for that.

Goodnight to you all

[evil__donkey]

Thanks for all the insightful replies. You people are great help. :-)