Northwind Question - Non Default Join on relationship to purchase order status table

[JMongi]

So I've been rooting around in the northwind sample database to get some ideas and feel for form interactions and such.

One thing I noticed is that there are a lot of specified non default joins. I understand the general ideas of inner/outer joins. But, they use them in areas I hadn't really thought about. As an example, there is a lookup table called "Purchase Order Status". That is connected in a 1:M relationship to table "Purchase Orders". Here's a pic:

So, if I understand correctly, a default query will pull all the records from PO and just the matching ones from PO Status.

I've always just did a default match join. It's seemed to work fine so far. But, maybe I've been doing it wrong. Can anyone fill in the blanks for me? They do the same thing with most other lookup type relationships. But, then, the "inventory transaction types" lookup table is a default join to the "inventory transactions" table. Just trying understand the thought process behind choosing the join type.

 

[JMongi]

So, is this just a case where they "hard coded" the default join type in the table structure?
I just created my own query and I could alter the join type between the tables to be whatever I wanted and it didn't affect the table relationship.

 

[MajP]

I've always just did a default match join. It's seemed to work fine so far. But, maybe I've been doing it wrong. Can anyone fill in the blanks for me? They do the same thing with most other lookup type relationships. But, then, the "inventory transaction types" lookup table is a default join to the "inventory transactions" table. Just trying understand the thought process behind choosing the join type.

The default join type is what the name says. Just a default. You can change it whenever you build a query, but it will default to what you set. Most people do not modify the simple inner join. If you know ahead of time you will always do an outer join then go ahead and change it. It will save you a few seconds later when building queries.
A lot of times you may build different queries that each end up using a different join, so I do not bother.

Read this

Relationships1 - Mendip Data Systems

Database applications for businesses, schools and individuals

www.mendipdatasystems.co.uk

 

[JMongi]

A lot of times you may build different queries that each end up using a different join, so I do not bother.

This makes much more sense to me. Thanks!

 

[MajP]

They only time I may bother defaulting the outer join is as a safety net. If I have a main table with a child table and only 50% of the main records have a related child record it is pretty easy to notice when I need an outer join to show all the parent records. But if the data table has related child records 98% of the time, it becomes a lot less obvious when a few records do not show up. So you start jumping through hoops trying to figure out why you query is only returning 9800 records instead of 10,000. Eventually you figure out you meant to have an outer join. So in that case I may set the outer join if I know that most of the time I want to show all parent records (not just those with a child record), and I know it might not be obvious when I screw up and need an outer join.

 

[JMongi]

Thanks for the additional insight!

 

[JMongi]

I believe I either have it downloaded already or have a thread bookmarked where you posted it.

I like your way of handling lookup lists and plan to incorporate it in my own database.

 

[JMongi]

I think Access was confusing me, and then my statement (based on my confusion) confused you!
A table relationship is as you described (1:1; 1:M) and I definitely want to enforce RI.
Also, a join, is related to a specific query construction where you specify how two or more tables are joined together.

Where Access was confusing me (and then me to you) is that it apparently allows you to "plan ahead" and specify the default join type between two tables if those two tables are subsequently included in a query. I had never worried about that before. I had always added a relationship between tables (which Access chooses automatically for us) and then left the default join type in the plan ahead portion to matching. Then, whenever I was creating my queries, I would then alter the default join if that was needed to construct the query properly. So, I never had any of those join arrows in my relationship diagram, only in my query builder.

I open the northwind database and I see join arrows all over the place in its relationship diagram and it threw me off. I thought I was missing something important.

Turns out it was just a different way of doing things.

 

[JMongi]

That matches up with what MajP said about using it as a safety net.

Also, thanks for the practical example about optional FKs. In my refresher reading on basic database design (from the other thread you know about ) I noticed this time through about optional relationships on the ERD but there wasn't a whole lot of explanation about what that would mean when mapping that to a table relationship. Your example helped illuminate that concept nicely. Thanks!