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!

 

[Pat Hartman]

The status table in the example is a "lookup" table. It's sole purpose is to provide a list of valid status codes and enforcing RI restricts the entries in the purchase order table to only the valid status codes.

There are several ways to handle lookup tables like this one. If you have only a couple in your app, developers generally just create a separate table for each one of them. They may or may not create a user interface to manage the entries. If they don't, then the developer must modify the table directly to add a new value to the list.

Another way is to embed the list in the table itself. This method always requires the developer to make changes so I wouldn't use it unless my list was never going to change like Gender. It is always Male, Female, Unknown. Or at least it always was. Once we adopt Sharia law, we'll have to modify the MaritalStatus table to allow for polygamy. Hope for the sake of the Mormons, they're allowed multiple wives also. At least the Koran limits the number of wives to 4 and makes the condition that they be treated equally. The number of concubines (sex slaves) is unlimited though.

As the developer, I don't want to be part of the day-to-day operation of the applications I develop so unless the table controls some programming action, I would always provide a way for the users to control their own lists. You can't allow them to delete items but you should add a third field so they can be inactivated. I've posted a mini-app that manages all simple lookup tables on many occasions. I import the objects into every new application I develop as soon as I discover the first lookup. Almost every app needs some lookups. I might as well use existing tested objects. That way as new lookups become necessary, I don't have to do any programming to accommodate them. I just add new tables to the list tables manager. If you can't find it but want it, PM me.

 

[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.

 

[Pat Hartman]

there are a lot of specified non default joins

I forgot to ask what you meant by this. YOU don't specify the type of the relation when you define it. Access determines whether it is 1-1 or 1-m by assessing the fields that are joined.

Access does allow you to add queries to the relationship diagram but I would never do that. They just clutter the picture. It also allows you to define the default join type. I do use this feature but only when the default is going to be a left join. Left joins are used when the Foreign Key is optional. For example, you want the rows from tableA even when there is no match in tableB. For example, if you have cars in a motor pool and you want to know who a car is assigned to but not all cars are permanently assigned, you use a left join between vehicles and employees.

There is a difference between a join and a relationship. A relationship is defined on the relationship window and is used to enforce Referential Integrity. If you are not going to enforce RI, there is no reason to bother defining how two tables normally connect. However, since you are working with a relational database, relationships are critical to ensuring the data is as good as you can make it so ignoring RI is downright foolish. A join is what we use in a query and although the vast majority of the joins will be identical to the relationship diagram, you may find cases where you need to join tables in other ways. As far as joins go, the only criteria is data type so you can join FirstName to Address if you want or CustomerID to VehicleID. Access doesn't care if the join makes sense only that the paired columns are compatible data types.

 

[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.

 

[Pat Hartman]

As I said, I actually use the default join type and I explained WHY. To expand on that, if you have an optional FK and you join to the other table and the FK is null, that record will not appear in the join.

Let me give you a different example.. Say that the ship to address data is optional on your order table. If the customer picks something up, you don't need to ship. However, State is the FK to the state table so if you do an inner join to tblState from tblOrders, pickup orders would not be included. So I would describe this join in the relationship diagram to remind me and subsequent developers that State is optional. Not for any other reason. Just a memory jog and a safety play (as we say in bridge). Access will automatically draw the left join rather than the default inner join for queries with these two tables. You can override it if you want to in a particular case.

 

[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!