Solved Determine Join Type Between Tables In MSSQL Diagram View?

[BlueSpruce]

Actually, the answer is a round about way is kind of funny!

SSMS designer lets you setup relationships, but it actually NEVER calls them anything!

(so, no names like “one to many” or “one to one” etc. is ever used, nor displayed).

On the other hand?

I don’t consider that a many to many relationships exists. You have involved 3 tables for this to occur!

And even for a one to one? Again, that's not a defined relationship in a database.

And such a relation only becomes one to one if you set a unique index on the FK. And often if not, most diagram tools don’t tell you this in the relationship window.

With the above choice (of relating a child table) then you can build any database on the planet.

I also NEVER use the term junction table with my dev team.

If you have a customer, and say you want to list out or enter their favorite kinds of foods?
(allow many kinds of favorite foods)
Well, to some, that means a junction table.
To me? No, it means I have a table called:

tblListOfFavoriteFoods.

It is a child table of the table customers – there is NO such defined term in relational database theory called "many to many”, or even "one to one"

Such things exist beyond that of having setting up a relationship.

So, many to many ONLY exists if you introduce a 3rd table, and relationships are NEVER setup between 3 tables – only between two tables!

Now of course to implement the above, we probably would use a sub form, and each row would have a combo box to select the favorite food. So, we would have the FK column (pointing up to the customer record, and then another column to hold the FavoriteFood_ID (that probably points to our table of favorite foods to choose from).


So, our mistake in Access land?
We assumed that a relationship setting exists to define that many to many. It does NOT. It is ONLY you the developer that decided to involve 3 tables to achieve this goal!

So, SSMS does not really show this information, because it’s a design choice, and one that is NOT enforced with a relationship.

Now, I suppose that the ER diagram “should” go and fetch MORE then the fact of a defined relationship exists. But, those additional meanings (such as one to one)? They are NOT actually part of the relationship, but are achieved by using additional settings in the database (such as setting a unique index on the FK column).

So, should this system figure out, and “tell you”?

Sure, perhaps, but then again, people would make the case for many such “telling the developer” all kinds of information!

The issue really centers on that we are “merging” the concept of a related table with that of concepts like “many to many” and now we stuck in the mindset that the relationship was setup that way. (NO, it was NOT!!!). It was the additional settings outside of the relationship settings that gave rise to this being a one-to-one relationship.

So, should additional concepts like what the tax rate is, or things like some unique index be displayed and you told as such in the ER diagram? Hum, difficult call.

It’s our failure to separate out the concept of a related table vs that of additional rules and constraints that we add AFTER creating that relationship.

If we are to get all “hoity-toity” and go all computer science theory here?

Codd’s rules for database theory NEVER does define a term “one to one” as a formal term!
One to one relationship arises from database design choices, not relational choices!

So, “one to one”, based on computer science writings?
It is NOT a feature nor property of the relational model, but such relationships arise from the developer choosing to enforce additional constraints.

So, at the computer science and theory level in our industry?

A one-to-one relationship is NOT part of the relational model!

You MUST choose additional design constraints beyond that, and things that are NOT PART of the relational setup that gives rise to the one-to-one outcome.

You in fact can’t define a one to one using ONLY relational database theory – you have to toss in additional things (constraints).

So, the above explains much why SSMS does not tell you more then that defined relationship, since database theory says that a relationship has ZERO to do with the relationship becoming a one to one – that’s a design choice achieved through constraints, not though the relationship, nor concepts of setting up a defined relationship.

And it also a developer design choice to say set some tax rates in the database. These are things that arise from developer design choices – not that we setup a relationship between two tables.

I mean, I really never worried much that SSMS does not hold my hand, and tell me the kind of relationship that setup, but is something that is not defined in the relationship anyway!

So, I always considered the type of relation being setup as a database design and constraint point of view, and I always kept the concept of the relationship between two tables a separate concept. Turns out SSMS also thinks this way….

I mean, for that customer example?
We have this in Access:

View attachment 122385

And we have this in SSMS:

View attachment 122386


Really, not much of an issue here!

So, the short answer?
A one-to-one relationship is not part of the relational database design spec.

So, we are confusing conceptual issues vs that of implementation and database design issues.

Access does not make these distinctions, and thus brings together multiple concepts,
but SSMS does not merge the concept of relations and that of constraints.

However, while in the SSMS ER diagram? You can right click on a table, and show the index(s).
And if you named the index with a "nice" name, like many to one?

Then you get/have this:
View attachment 122387
So, you can name the index or constraint with something that means something to you the developer....

R
Albert

An Access multi-valued field represents a many-to-many relationship by creating a hidden junction table behind the scenes.

 

[Albert D. Kallal]

An Access multi-valued field represents a many-to-many relationship by creating a hidden junction table behind the scenes.

LOL!!!! - don't get me started on that issue! (as a FYI: I actually was one of the few Access developers that supported this amazing feature!!!).

R
Albert

 

[Albert D. Kallal]

Ok, I HAVE to do a follow up post!
One of the things I love by helping and posting to the community here?

Well, you find out VERY interesting things!

While I made a long winded post about how SSMS does not really tell you the difference between a one to one, and one to many?

I am WRONG!!! - some nice eggs on your face Albert!!!

As pointed out? If I have a one to many, then you see this:

Ok, now go to table design mode, and add a unique index to the table (to tblCustomer_ID). This would make/force this to be a one to one.

Doing so, now lookey!! - you see the key!!!

I never really noticed that SSMS does this!
However, to be fair, if you build/draw/make the diagram first and draw the join line?
And THEN you go to the table design and add the unique index to the table?
The diagram does not update.
So, this probably explains why I never noticed above.

Hence, if you make such a change? Then remove the relationship. Add the unique index.
Then in the diagram, add the relationship back - it should now show the yellow PK symbol on each end.....
Not 100% sure how you can "refresh" the diagram without having to delete the relationship, and re-creating.
But, wonders of wonders - as pointed out, the diagram DOES show the correct symbols for a one to one, or a one to many...

Does not change my narrative above, but my my point about SSMS not showing one to many, and one to one?
It does!!! (or perhaps better stated, it can). My order of creating such tables, then building relationships using diagram, and then creating the index?
I don't as a general rule thus see a PK key on each end......

R
Albert

 

[BlueSpruce]

Ok, I HAVE to do a follow up post!
One of the things I love by helping and posting to the community here?

Well, you find out VERY interesting things!

While I made a long winded post about how SSMS does not really tell you the difference between a one to one, and one to many?

I am WRONG!!! - some nice eggs on your face Albert!!!

As pointed out? If I have a one to many, then you see this:
View attachment 122390


Ok, now go to table design mode, and add a unique index to the table (to tblCustomer_ID). This would make/force this to be a one to one.

Doing so, now lookey!! - you see the key!!!

View attachment 122391

I never really noticed that SSMS does this!
However, to be fair, if you build/draw/make the diagram first and draw the join line?
And THEN you go to the table design and add the unique index to the table?
The diagram does not update.
So, this probably explains why I never noticed above.

Hence, if you make such a change? Then remove the relationship. Add the unique index.
Then in the diagram, add the relationship back - it should now show the yellow PK symbol on each end.....
Not 100% sure how you can "refresh" the diagram without having to delete the relationship, and re-creating.
But, wonders of wonders - as pointed out, the diagram DOES show the correct symbols for a one to one, or a one to many...

Does not change my narrative above, but my my point about SSMS not showing one to many, and one to one?
It does!!! (or perhaps better stated, it can). My order of creating such tables, then building relationships using diagram, and then creating the index?
I don't as a general rule thus see a PK key on each end......

R
Albert

An ahaa moment.
Did the MS team that created the Access ER diagram model it after SSMS's diagram, or vice versa?
Does the SSMS ER also show many-to-many (infinity symbols on both endpoints)?

 

[DickyP]

LOL!!!! - don't get me started on that issue! (as a FYI: I actually was one of the few Access developers that supported this amazing feature!!!).

R
Albert

Sorry but MVFs are dreadful on so many levels - the single worst Access innovation ever!

 

[BlueSpruce]

Sorry but MVFs are dreadful on so many levels - the single worst Access innovation ever!

Access MVF's are useful. It's clever how they implemented that feature. You just have to know their limitations and how to work with them.

CAVEAT: If you migrate an Access db to SQL Server, the MVF is converted to an ntext field that contains the delimited set of values. SQL Server does not support MVF's that model a many-to-many relationship, so design and conversion mods are needed.

https://support.microsoft.com/en-gb...ed-field-7c2fd644-3771-48e4-b6dc-6de9bebbec31

 

[Imb]

Access MVF's are useful. It's clever how they implemented that feature. You just have to know their limitations and how to work with them.

In a certain niche MVF's can be useful.
But when continuing on generalization, to make forms independant of the user content, I am afraid they work in the wrong direction.

 

[GPGeorge]

My position is that experienced developers who understand how MVFs are designed and how to implement them are more likely to avoid them in favor of more robust approaches.

Novices who don't understand normalization, much less the inner workings of MVFs, are more likely to use them and do so inappropriately.

Like a lot of tools, in the wrong hands, they do more harm than good. In the right hands, they can be useful. Unfortunately, they appeal more to those less likely to use them safely.