Quick question on 2005 relationship diagrams

sql 2005 management studio 2005
When building and setting up a table diagram with the sql 2005 tools, is
there a way to Distinguish between a left join, and inner join?
For the most part from a technical point of view, the two enforced
relationships are NOT different, but from a *developers* point of view, I
can thus infer that the designer of the database application will assume
that a child record needs to be created when you setup the relationship as
a (inner join).
And, of course a child record does NOT need to be created by the application
when the relationship is setup as a left join. For the most part here the
distinction between the two different joins is really ONLY for documentation
purposes to the developer(s), and does not change the actual referential
integrity enforced by the data engine.
Is there any means in the database diagramming tools to distinguish between
the above two types of joins?
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal@.msn.comShort answer: No.
Long answer:
The diagram is a representation of your tables and constraint. A foreign key (FK) constraint say
that a row in the referencing table cannot exist unless there's a value in the referenced table for
the FK column. A FK constraint do not say that you need a row in the referencing table in order for
a row in the referenced table to exist. That would be difficult, one row has to come before the
other!
One thing you can do is to add comments to the diagram. That would not cause the joins to be
"automagically created" in any particular way, but it might assist the programmer.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Albert D. Kallal" <PleaseNOOOsPAMmkallal@.msn.com> wrote in message
news:OUPaEk8cIHA.4172@.TK2MSFTNGP02.phx.gbl...
> sql 2005 management studio 2005
> When building and setting up a table diagram with the sql 2005 tools, is there a way to
> Distinguish between a left join, and inner join?
> For the most part from a technical point of view, the two enforced relationships are NOT
> different, but from a *developers* point of view, I can thus infer that the designer of the
> database application will assume that a child record needs to be created when you setup the
> relationship as a (inner join).
> And, of course a child record does NOT need to be created by the application when the relationship
> is setup as a left join. For the most part here the distinction between the two different joins is
> really ONLY for documentation purposes to the developer(s), and does not change the actual
> referential integrity enforced by the data engine.
> Is there any means in the database diagramming tools to distinguish between the above two types of
> joins?
> --
> Albert D. Kallal (Access MVP)
> Edmonton, Alberta Canada
> pleaseNOOSpamKallal@.msn.com
>|||The diagramming tool doesn't document joins; it documents constraints. You
would check the nullability of the referencing column to determine if you
would use an inner or outer join when retrieving data.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Albert D. Kallal" <PleaseNOOOsPAMmkallal@.msn.com> wrote in message
news:OUPaEk8cIHA.4172@.TK2MSFTNGP02.phx.gbl...
sql 2005 management studio 2005
When building and setting up a table diagram with the sql 2005 tools, is
there a way to Distinguish between a left join, and inner join?
For the most part from a technical point of view, the two enforced
relationships are NOT different, but from a *developers* point of view, I
can thus infer that the designer of the database application will assume
that a child record needs to be created when you setup the relationship as
a (inner join).
And, of course a child record does NOT need to be created by the application
when the relationship is setup as a left join. For the most part here the
distinction between the two different joins is really ONLY for documentation
purposes to the developer(s), and does not change the actual referential
integrity enforced by the data engine.
Is there any means in the database diagramming tools to distinguish between
the above two types of joins?
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal@.msn.com|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message
> One thing you can do is to add comments to the diagram. That would not
> cause the joins to be "automagically created" in any particular way, but
> it might assist the programmer.
>
Yes, the assisting of the developers at design time is the goal here. You
well grasp my reasons for wanting to note the type of join. And, by the way,
I am likely going to adopt your suggestion of placing comments in the
diagram.
Coming from a ms-access background, the Relationship designer *did*
distinguish between the two type of joins. (left join, and inner join). As I
said, this does nothing to the actual behavior of the data engine, but you
get 2 nice benefits.
1) when you do build joins, the query builder can create the join based on
your original design assumptions. (left, or inner)
2) Any other developer can *note* the design assumptions I made by simply
looking at the ER diagram. The big question this answers is does the
code/application assume that I have to create a child record or not when a
parent record is created?
Note that in a typical application, only about 10%, or even less of the
relationships assume that a child record must be created when a parent
record is made.
So, I just wanted a way to document and covey this issue to developers, and
comments in the diagram will have to do.
Thanks a bunch for the input...
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal@.msn.com|||> Coming from a ms-access background, the Relationship designer *did*
> distinguish between the two type of joins. (left join, and inner join). As I
> said, this does nothing to the actual behavior of the data engine
It is an interesting concept to add meta-data for this type of information so that tools like
query-builder can be smarter (guess what type of join to perform). The place for this would be the
system tables, exposed through catalog views. Possibly as extended properties, assuming that tool
vendors could agree on the structure...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Albert D. Kallal" <PleaseNOOOsPAMmkallal@.msn.com> wrote in message
news:%23taEL4%23cIHA.3736@.TK2MSFTNGP04.phx.gbl...
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message
>
>> One thing you can do is to add comments to the diagram. That would not
>> cause the joins to be "automagically created" in any particular way, but
>> it might assist the programmer.
> Yes, the assisting of the developers at design time is the goal here. You
> well grasp my reasons for wanting to note the type of join. And, by the way,
> I am likely going to adopt your suggestion of placing comments in the
> diagram.
> Coming from a ms-access background, the Relationship designer *did*
> distinguish between the two type of joins. (left join, and inner join). As I
> said, this does nothing to the actual behavior of the data engine, but you
> get 2 nice benefits.
> 1) when you do build joins, the query builder can create the join based on
> your original design assumptions. (left, or inner)
> 2) Any other developer can *note* the design assumptions I made by simply
> looking at the ER diagram. The big question this answers is does the
> code/application assume that I have to create a child record or not when a
> parent record is created?
> Note that in a typical application, only about 10%, or even less of the
> relationships assume that a child record must be created when a parent
> record is made.
> So, I just wanted a way to document and covey this issue to developers, and
> comments in the diagram will have to do.
> Thanks a bunch for the input...
> --
> Albert D. Kallal (Access MVP)
> Edmonton, Alberta Canada
> pleaseNOOSpamKallal@.msn.com
>
>