Relationships for Dummies

[Winterwolf73]

Ok all. As most of you have already figured out I am pretty new to building a database from scratch. From the advice (which was a bit of a slap in the face but well needed) I have received here, I did a bit more research and have cleaned up what I have built so far. One of the things I found in my research was relationships between tables. This is an easy concept to understand. However, when I read about the "Join Type" options this is where I get fuzzy.
So here is the layout of the tables.
I have a table for my customer information. This table is needs to be linked with the following tables: Invoices, Projects, and Payments. My Projects table needs to be linked with the Expenses, Materials, and Suppliers tables.

Each customer will more than likely have more than one invoice/project/payment entries. Each project will have more than one expense entries. Where I get fuzzy is which "Join Type" option to use. I have looked for a couple hours to find a dumb down explanation of not only what the option mean, but how they work.
Could someone provide a dumb down explanation of these options please? You would be a HUGE lifesaver.
Thanks,

 

[theDBguy]

Hi. See if this article helps. Please pay close attention to the second to the last paragraph.

Query Join Basics

Recently, I have noticed a few developers, new to Access, showing up in the forums and were confused by the different JOINs that can be used...

thedbguy.blogspot.com

 

[Winterwolf73]

now I am even more confused. I am not doing this in a query, but setting up the relationship between tables. Once I drag invoice number, for example, from the customer table to the invoice table the same window pops up.

 

[theDBguy]

now I am even more confused. I am not doing this in a query, but setting up the relationship between tables. Once I drag invoice number, for example, from the customer table to the invoice table the same window pops up.

Hi. Did you read the second to the last paragraph?

PS. I just reread the article, and I guess I was a little vague there. Basically, what I meant to say there was Join Types don't apply in table relationships. Thay should always be Inner Joins, just to use a name. But, the article should explain the difference between the join types, if that will help you understand them.

 

[MajP]

I have not read @theDBguy blog yet. However, this is a great article by @isladogs. It is probably the best I read.

Relationships1 - Mendip Data Systems

Database applications for businesses, schools and individuals

www.mendipdatasystems.co.uk

 

[isladogs]

Thanks for the positive feedback @MajP.
In case it helps the OP, I also have an article discussing the various Query Join Types

 

[plog]

The best way to complete your Relationship Tool is by adding 1 table at a time. So I suggest you clear out every table from the tool and start with your "main" table. This isn't an exact science, but the main table is the one that is at the center of your database and is often connected to the most tables. Most likely tblInvoices.

Next, add tblProjects and link it to tblInvoices appropriately. This might mean adding [ProjectID] to tblInvoices so that you can relate them. Now comes the hard part--adding the other tables at the appropriate level. Pick a table to add and ask yourself, "which 1 table in the relationship tool does this directly relate to?". It cannot relate to 2 existing tables, you can only connect it to one table.

Do that for your tables 1 by 1, adding [_ID] fields to tables as necessary to connect them. Then take another screenshot and post back here.

 

[MajP]

It cannot relate to 2 existing tables, you can only connect it to one table.

@plog
Not sure what you meant to say here. For the OP, a table may have multiple foreign keys in it so every foreign key relates to another table. Also, many tables can can have the same foreign key relating back to the same table. So it can be a many to many.

 

[plog]

It cannot relate to 2 existing tables, you can only connect it to one table.

It = new table to the relationship tool. Using my method as described (one table at a time) the new table will be related to just 1 table when it is added.

 

[Pat Hartman]

Using "ID" as the PK name for every table is just confusing and makes mapping PK-FK more difficult than it needs to be for someone who is not familiar with your schema and who is not actually looking at the database diagram. Name the PK with words related to the table name such as CustomerID and InvoiceID. Personally, I reserve the use of "ID" as a suffix for only fields that are autonumbers. If the PK is a string, I would use "CD" as the suffix. If the PK is numeric but comes from elsewhere so that it is not an autonumber, I would use "NUM" as the suffix.

When you add a FK to a table, use the PK as its name whenever possible. There are two cases when you would have to deviate. First when you need to use the same FK multiple times as you would if you have something like Billing and Shipping addresses in the same table. In that case I would use BillSTID and ShipSTID to avoid confusion. The second case is if your table is self referencing as employee tables usually are. SupervisorID is actually a reference to a different employee's EmployeeID.

Your tables do not seem to include FK's at all so they would need to be added.

And finally, it is poor practice to use embedded spaces or special characters in your object names so I would get rid of them. Personally, I prefer CamelCase for object names. Other people prefer the_underscore. I don't like having to shift when I type so I only use the_underscore for emphasis and that is rarely needed.

If you post your database, I'll give the relationships a whirl to get you started if you want help.

 

[onur_can]

read this article thoroughly.
Table Relations Guide
see also
Table design

 

[isladogs]

Basically, what I meant to say there was Join Types don't apply in table relationships. Thay should always be Inner Joins, just to use a name.

Sorry but I disagree. Although relationships usually have inner joins which is the default option, that doesn't have to be the case.
Outer joins are also supported and at times appropriate.

In fact it is possible to use outer joins and set referential integrity with cascade update/delete.
This is an example from the third section of my Relationships article mentioned in post #5

 

[theDBguy]

Sorry but I disagree. Although relationships usually have inner joins which is the default option, that doesn't have to be the case.
Outer joins are also supported and at times appropriate.

In fact it is possible to use outer joins and set referential integrity with cascade update/delete.
This is an example from the third section of my Relationships article mentioned in post #5

View attachment 84201

@isladogs When I said "Join Types don't apply in Table Relationshps," I didn't mean you can't do/use them in Access. For example, so your article demonstrated an OUTER JOIN relationship, but what does it mean to the structure of the tables?

The question I ask myself to come to my conclusion was, "What is the T-SQL equivalent of a Join Type when creating a table relationship in SQL Server?"

Create foreign key relationships - SQL Server

Create foreign key relationships in SQL Server by using SQL Server Management Studio or Transact-SQL.

docs.microsoft.com

PS. I don't have access to SSMS right now; but if you do, can you even create an OUTER JOIN when establishing table relationships in SQL Server?

 

[isladogs]

@theDBguy
Not sure I follow.
Relationships don't affect the structure of the tables though the opposite is obviously true

The article includes an example database so you can check out how that works in practice if you wish.
I'll attach it to this post as well in case that helps.

I'm also unclear what you are asking with regard to SQL Server but will log in to SSMS later and hopefully respond to that question

 

[theDBguy]

@theDBguy
Not sure I follow.
Relationships don't affect the structure of the tables though the opposite is obviously true

The article includes an example database so you can check out how that works in practice if you wish.
I'll attach it to this post as well in case that helps.

I'm also unclear what you are asking with regard to SQL Server but will log in to SSMS later and hopefully respond to that question

Maybe, if I can find more time later on, I'll expound on the blog article to tackle "table relationship" specifically. To me, and maybe I'm way off, it just seems "join types" don't mean anything at all when it comes to table relationships.

As a broad example, we may think about parents having a one-to-many relationship with their children. Other than that notion, how do we describe "join types" as far as relationships between parents and children are concerned? My brain can't comprehend that question. Because to me, "join types" only make sense when you talk about Queries - not Table Relationships.

In my brain, those two thing are separate. We can create a relationship between two tables, but never query them together (although that's probably rare). On the other hand, we can also NOT create a relationship between two tables, but still able to create a query between them, using any of the join types.

Sorry if I'm just being confused about it.

As far as SQL Server is concerned, I was just thinking SQL Server is (could be) considered more of a true relational database management system than Access. If so, I was just curious how you would create a table relationship with OUTER JOINs in a true RDBMS. We can apply this question to other systems as well, i.e. MySQL, Oracle, PostgreSQL, etc. Do they allow "Join Types" when creating a table relationship (basically, when creating a foreign key constraint)?

PS. I just realized what I really mean when I say "Join Types" don't apply to Table Relationships. If, for example, Microsoft updates the UI for the "Edit Relationships" window and removed (or disabled) the "Join Type.." button? What impacts would that have to the database? In my humble opinion, that button does not belong in the Edit Relationships Window.

Just my 2 cents...

 

[isladogs]

I'm sorry but you've now lost me completely.

At the risk of restating the obvious, the main purpose of relationships is to set referential integrity.
Queries based on two or more tables will work fine whether or not a relationship has been applied.
However if a relationship has been applied, that will automatically show in a query.
Nevertheless the join between two tables in a query does not need to be the same as in the underlying relationship.

My extended article covers all of this and more, with examples of each relationship discussed.
The link to the first part is http://www.mendipdatasystems.co.uk/relationships1/4594533224

I also don't understand why you have mentioned other systems such as SQL Server.
In general, these allow additional join types that aren't permitted in Access e.g. Full outer joins

 

[theDBguy]

I'm sorry but you've now lost me completely.

At the risk of restating the obvious, the main purpose of relationships is to set referential integrity.
Queries based on two or more tables will work fine whether or not a relationship has been applied.
However if a relationship has been applied, that will automatically show in a query.
Nevertheless the join between two tables in a query does not need to be the same as in the underlying relationship.

My extended article covers all of this and more, with examples of each relationship discussed.
The link to the first part is http://www.mendipdatasystems.co.uk/relationships1/4594533224

I also don't understand why you have mentioned other systems such as SQL Server.
In general, these allow additional join types that aren't permitted in Access e.g. Full outer joins

Hi @isladogs. Just in case you missed it, I added a Post Script paragraph to my post above. My point was, and I guess I could be wrong, that "Join Types" don't apply to Table Relationships. To me, they only make sense in Queries. In other words, I only know how to say "one-to-one or one-to-many or many-to-many" relationships. I don't know how to say "left/right/full join" as a "relationship" concept. To me, they are just "joins."

If that's still confusing, you're not alone. I think I am confused as well.

PS. Regarding your article showing the different join types in a table relationship, and in relation to the PS I added to my post earlier, how would you have written that article, if the "Join Type" button was not available?

 

[theDBguy]

I also don't understand why you have mentioned other systems such as SQL Server.
In general, these allow additional join types that aren't permitted in Access e.g. Full outer joins

You may be thinking about them in the context of using them inside a SELECT statement (or other action queries too).

My question was how do they apply (outer joins) when creating a foreign key constraint?

 

[isladogs]

You added your postscript after I wrote my reply.
I still disagree. Although the default relationship is an inner join, there can be perfectly valid reasons for applying outer joins in table relationships.

Please read my article

 

[theDBguy]

You added your postscript after I wrote my reply.
I still disagree. Although the default relationship is an inner join, there can be perfectly valid reasons for applying outer joins in table relationships.

Please read my article

Okay, I will. Thanks!