The Junction Table?? 1

[pfenton]

What is a Junction table and what are it's uses and benefits?
I've never heard of them before so any info would be appreciated!

 

[Telsa]

I think you are talking about a table that joins two other tables together based on their primary keys (making them foreign keys I believe). It can contain just the two fields.


Does that make sense?

Mary )

 

[kathryn]

An example would be a library system, which contains authors and books. Each author can write many books, and each book can have many authors. Note that not every book WILL have many authors and not every author WILL write many books, but the MIGHT! That is the key to database design; thinking about what might, in the future or if the business changes, or if Jupiter aligns with Mars, happen.

Anyway, you need away to keep track of what authors wrote what books.

One, very bad way is a table with X number of fields. The first is the title, all the others are authors.

Gone with the Wind, Margaret Mitchell
The Firm, John Grisham
The Rainmaker, John Grisham
Access 97 Developer's Handbook, Paul Litwin, Mike Gilbert, Ken Getz

You could turn this around 180 degrees and make a table with author and multiple fields for titles, but that would be a lot of fields, for Isaac Asimov, for example.

Instead we use a junction table to represent the relationship.

Here are the tables:

tblBookTitle
------------
BookTitleID (Autonumber Primary Key)
BookTitle


tblAuthor
---------
AuthorID (Autonumber Primary Key)
AuthorLast
AuthorFirst


tblBookAuthor
-------------
BookAuthorID (Autonumber Primary Key)
BookTitleID
AuthorID


Data would look like


tblBookTitle

1 Gone with the Wind
2 The Firm
3 The Rainmaker
4 Access 97 Developer's Handbook

tblAuthor

1 Mitchell Margaret
2 Grisham John
3 Litwin Paul
4 Mike Gilbert
5 Paul Litwin


tblBookAuthor

1 1 1 (Gone with the Wind is BookID1 Mitchell is AuthorID 1
2 2 2 (The Firm is BookID 2 Grisham is AuthorID 2)
3 3 2 (The Rainmaker is BookID 3 Grisham is AuthorID 2)
4 4 3 (Access 97 Developer's Handbook BookID 4 Litwin is AuthorID 4)
5 4 4 (Access 97 Developer's Handbook BookID 4 Gilbert is AuthorID 5)
6 4 5


The reasons to use this are numerous. Your author names and book titles exist once, eliminating data entry errors. Your forms and reports in Access are easy to design, once you realize that all your work will be in the tblBookAuthor, using the other two tables as lookup for the BookID and AuthorID.

The Access 97 Developer's Handbook by Litwin, et al, mentioned above has an excellent discussion of database design which goes into this very will. I highly recommend it if you are going to be seriously working with Access.

Good luck and post if you have questions.

Kathryn

 

[pfenton]

Kathryn,
You must be my guardian angel!!!
Thanks very much, well explained, very helpful!
Is that 2 pints of Harp I owe you now????

 

[kathryn]

I'm thinking at least three! Have a good day (and TGIF! have a good weekend.)

Kathryn