On one to one relationships data will only be inserted into the second
table if that information is provided. That is it doesn't place a
bunch of null values just because you added and item to the first table
but do not define any values to the second table, there by the first
table can have thousands of rows, but the second table could have only
one item.
Correct?
Oh another dumb question. What is the advantage of one to one verses
having everything in a flat table with a bunch od spaces?
-TIA-> On one to one relationships data will only be inserted into the second
> table if that information is provided. That is it doesn't place a
> bunch of null values just because you added and item to the first table
> but do not define any values to the second table, there by the first
> table can have thousands of rows, but the second table could have only
> one item.
That's what I'd do (not add the row in the second table unless it adds
value).
> What is the advantage of one to one verses
> having everything in a flat table with a bunch od spaces?
Sometimes tables get wider than the allowed 8000-odd bytes and you HAVE to
create an extension table. If this is happening, the data may need to be
normalized instead of growing wider or just adding an extension table.
Sometimes you are adding on to a 3rd party database and don't want to (or
aren't allowed to) modify their schema.
Sometimes for performance reasons you may partition a table into separate
tables based upon the way the data is accessed (which takes a good level of
understanding of performance and your data because the gains can be lost by
potentially needing extra JOINs) so that you aren't reading these wide
tables and incurring more I/O than necessary. Though the extra I/O can
often be avoided by using covering indexes.
Sometimes your the item you are representing in a table has 200 possible
attributes but only a small subset are used for every record and the other
attributes are not used often. You can save disk space by keeping your main
table narrow and only adding records in the other table(s) when you need the
attributes. This could also help or hurt performance (see above). This can
commonly be the case when your database structure mirrors an object
hierarchy -- your "base" class is a table and you may have extension tables
for derived classes that contain extra attributes.
I'm sure there's a lot more to discuss on each of those items but that
should give you a sample of what you would use an extension table for.
Mike|||"Matthew" <MKruer@.gmail.com> wrote in message
news:1145647943.503465.33430@.e56g2000cwe.googlegroups.com...
> On one to one relationships data will only be inserted into the second
> table if that information is provided. That is it doesn't place a
> bunch of null values just because you added and item to the first table
> but do not define any values to the second table, there by the first
> table can have thousands of rows, but the second table could have only
> one item.
> Correct?
> Oh another dumb question. What is the advantage of one to one verses
> having everything in a flat table with a bunch od spaces?
> -TIA-
>
Disk usage and performance, for one thing. Partition the table so that
frequently used columns are in one table, and the rest in the other.
Narrower the table, the more rows fit on a single page, and thus less IO.
Also, extensive usage of NULLs should be avoided, if possible.
Dean|||Thanks for the replies guys. It is good to hear what I though from
someone else.
Not to get into too much detail but the reason why I wanted to make
sure is that every object for the entire DB is listed in the first
table however not every object requires extended detailed information.
I am breaking into like items or more precisely items that are grouped
together (required)|||Oh one more question about referential integrity.
If I decide to add a third table and link it to the second table that
means in order for the third table to be used, the second table need to
have the data in it? That is that unless you have the required data in
the second table, you can just make a reference form the first to third
table with out it going through the second table. .
Correct?|||Matthew,
If we're still discussing partitioning a wide table into two, or three, or
even four narrower tables (although it's a bit extreme, imho), then all
those tables should share the common primary key. IOW, both the second and
the third table should hold a refference to the first table - via the first
table's primary key.
Dean
"Matthew" <MKruer@.gmail.com> wrote in message
news:1145651871.899798.9550@.e56g2000cwe.googlegroups.com...
> Oh one more question about referential integrity.
> If I decide to add a third table and link it to the second table that
> means in order for the third table to be used, the second table need to
> have the data in it? That is that unless you have the required data in
> the second table, you can just make a reference form the first to third
> table with out it going through the second table. .
> Correct?
>
Showing posts with label relationship. Show all posts
Showing posts with label relationship. Show all posts
Tuesday, March 20, 2012
Friday, March 9, 2012
Quick Question on Relationships
Hopefully, this is an easy one for people with more experience with SQL than me...
Is it possible to create a foriegn key relationship between two tables in SQL that relates one column in the primary table to two columns in a secondary table?
For example, say you have a table with user account information, and you want to store some type of interaction (some type of correspondence, for example) between two users. The second table (used to store interaction), among other things, stores the primary keys that indentify the two user profiles (in this scenario, say the primary key for the users is a single column surrogate key). So you store both user's keys in the second table in their own columns (one to note who started the correspondence, the second to note who the correspondence was sent to).
Is there a way to define (in my mind, I see it as two) relationships that would relate the primary key column to both columns in the second table so you can cascade a delete? Enterprise Manager for SQL2k won't let you do this (at least that I can find).
Is there the type of scenario that a Trigger would come into play?Do you mean like:
USE Northwind
GO
SET NOCOUNT ON
CREATE TABLE myUsers99(UserId int PRIMARY KEY, UserType varchar(10))
CREATE TABLE myNotes99(UserId1 int, UserId2 int, Notes varchar(255)
, FOREIGN KEY (UserID1) REFERENCES myUsers99(UserId)
, FOREIGN KEY (UserID2) REFERENCES myUsers99(UserId)
)
GO
SET NOCOUNT OFF
DROP TABLE myNotes99
DROP TABLE myUSers99
GO|||It's called a Many-To-Many relationship.
The values in each dataset can be related to more than one value in the other dataset through an intermediary table. In your case, both datasets are the same table.|||Exactly, except I want to cascade deletes to the child table. If a user is deleted from myUsers99 then any rows in myNotes99 that reference that user ID will cause errors in the code. If you select the Cascade Delete checkbox, then Enterprise Manager throws an error when saving the changes that it creating the contraints "may cause cycles or multiple cascade path", which is actually what I want.
I could do this pretty easily through code, but I have become a fan as of late of moving as much RI maintenance into the database layer instead of runtime.|||Very dangerous. You better be damn sure of your record relationships.
You are best off doing this using a stored procedure to delete your records, but if you insist on having it tied to the schema then you could do it in a trigger.|||Blindman and Brett, thanks for the quick answers. It looks like I will rethink this idea and go about it a little differently.|||Very dangerous.
Ummm...maybe...
I never use cascading updates or deletes, and I do prefer to do all the work in a stored procedure.
As a matter of fact, I prefer to totaly isolate developers to only have EXEC authority to sprocs, so I know that no errant DML will cause any data integrity issues.
Also, the fact of having as much constraints isloated to the database the better...
So without furth ado...my first INSTEAD OF TRIGGER
USE Northwind
GO
SET NOCOUNT ON
CREATE TABLE myUsers99(UserId int PRIMARY KEY, UserType varchar(10))
CREATE TABLE myNotes99(UserId1 int, UserId2 int, Notes varchar(255)
, FOREIGN KEY (UserID1) REFERENCES myUsers99(UserId)
, FOREIGN KEY (UserID2) REFERENCES myUsers99(UserId)
)
GO
CREATE TRIGGER myTrigger99 ON myUsers99 INSTEAD OF DELETE
AS
BEGIN
DELETE FROM myNotes99
WHERE UserID1 IN (SELECT UserId FROM deleted)
OR UserID2 IN (SELECT UserId FROM deleted)
DELETE FROM myUsers99
WHERE UserId IN (SELECT UserId FROM deleted)
END
GO
INSERT INTO myUsers99(UserId, UserType)
SELECT 1,'Manager' UNION ALL
SELECT 2,'Client' UNION ALL
SELECT 3,'Scrub'
GO
INSERT INTO myNotes99(UserId1, UserId2, Notes)
SELECT 1,3,'Get to work' UNION ALL
SELECT 1,2,'Have a nice day' UNION ALL
SELECT 1,1,'Note to self...fire scrub'
GO
SELECT * FROM myNotes99
GO
DELETE FROM myUsers99 WHERE UserID = 1
GO
SELECT * FROM myUsers99
GO
SELECT * FROM myNotes99
GO
SET NOCOUNT OFF
DROP TRIGGER myTrigger99
DROP TABLE myNotes99
DROP TABLE myUSers99
GO|||NEVER use cascading updates and deletes?
Would you NEVER use a self-cleaning oven?
NEVER use cruise control?
NEVER use your home's thermostat?
Implementing cascading updates and deletes doesn't preclude you from limiting user access only to stored procs. It just means you don't have to duplicate built-in functionality with custom code!|||NEVER use cascading updates and deletes?
No. Cascading Updates. Wouldn't you consider that a new Entity? Wouldn't you want to keep history? Oh, wait, you're in the surrogate camp? Yes
Would you NEVER use a self-cleaning oven?
Never...the wife does though
NEVER use cruise control?
Actually, no, I don't...I live in the New York Metro Area...not a chance
NEVER use your home's thermostat?
I assume you mean a programmable one...I haven't figured out to program yet...(I defer to the missus...what say would I have over what the temp was)
Implementing cascading updates and deletes doesn't preclude you from limiting user access only to stored procs. It just means you don't have to duplicate built-in functionality with custom code!
For Key information only...And wasn't your earlier point that it was dangerous?
And whether it was with a trigger, constraint or a sproc, what's the difference?
OK, Here's one. Any inadvertant DML and with cascading, you could blow away an entire relational tree...ooops...
I guess you could always have a recovery procedure if you kept history|||And whether it was with a trigger, constraint or a sproc, what's the difference?the difference is how much time you have to spend writing it
:)|||Ha ha! I knew that post would provoke a heated response! ;)
As far as your comment about inadvertently blowing away a whole relational tree, if you duplicate cascading deletes in your stored proc then you may have shifted that danger, but you haven't eliminated it.|||Relying on CASCADE ON <whatever> is like believing that when you tell your wife to fill up the car, you can hope that she would fill up the windshield wipers flued which light has been on for the past 2 weeks. But guess what, - SHE DOESN'T!!! I guess the pont is that this method is for lazy DBA's, but I guess I am even lazier, which means I don't want to remember 6 months down the road that "there was a CASCADE ON DELETE last time I checked, I swear..." WRITE ONCE, REVISIT...NEVER!!!|||Comment deleted upon consideration of better judgement...|||Comment deleted upon consideration of better judgement...Hey, I liked the original post, before editing :D
Comparing it to a wife is a really unfair!|||Wow...lots to chew on!! Thanks for everyone's input! In this particular case, I am not really concerned with maintaining a history because this data is not very important. So if it gets lost its not a big deal and broken relationships will case problems because the hierarchy being consistent is pretty important for this (lesser of two evils).
In frankness, the user table will almost never have a row (user) deleted. Which is all the more reason to try and plan this in the DB rather than through a SP so programmers don't have to remember that they have to clean up such-and-such table if they delete a row from so-and-so table.
I guess if I used the deleted table method as Brett suggested I could maintain RI (just have to check both tables for messages instead of one) and set some type of scheduled batch to clean up that table from time to time ...just seems like a waste of space to lug around data that shouldn't ever be used in daily operation of the code.
I agree with blindman. Why re-invent the wheel and maintain RI through SP, Triggers, or code when the database can handle it for you automagically? Some may find it lazy (and I agree that it is if you over-rely on it), but its great most of the time.
I know MS probably has a good reason for not allowing this, but it just seems like it would a nice feature (granted, it would probably rarely be used) to be able to link one column in a table to two in another and be able to cascade deletes or updates.|||Well you have a special case, and that's one of the ways to do it.
But Cascading for me has a bigger philisopgical issue. Updates especially. You are not just messing with data.
You're messing with the key.
So if the key of a state table for New Jersey is NJ, and you want to change that key with a cascade update to all of it's children to XX, sure it's easy, but does it make sense?
Wouldn't NJ "live" historically? Wouldn't XX be a brand new entity?
While this thought doesn't apply to Deletes, Deletes are just dangerous. You want to delete a parent and all of it's children, fine, take care of it in a sproc.
MOO|||Wouldn't NJ "live" historically? only in your mind
BARK|||I don't think it would exist.
Example (admittedly very hypothetical, but go with it): If 'NJ' is being updated in your database to be something like 'N.J.' (another example is what happens if New Jersey changes its state name to 'Jersey' and the abbreviation becomes something like 'JY'), then wouldn't you want your child tables to cascade the change? If your code uses the foriegn key in the child table to look up the parent row to get the full name of the state, and you didn't cascade the update, then your code is now broken (or has to be re-written to handle the new state abbreviation...neither is an acceptable option in my opinion).
If the keys are important enough to define a relationship for, I personally think they should be important enough to cascade deletes and updates (assuming of course that the two keys are actually used *in practice* to reference parents/children and have the possibility of changing or being deleted...I know there will be exceptions to that thought). Otherwise, why use a relationship?
In my experience, it saves many-o-bottle of aspirin if the database maintains itself rather than the developers.
Is it possible to create a foriegn key relationship between two tables in SQL that relates one column in the primary table to two columns in a secondary table?
For example, say you have a table with user account information, and you want to store some type of interaction (some type of correspondence, for example) between two users. The second table (used to store interaction), among other things, stores the primary keys that indentify the two user profiles (in this scenario, say the primary key for the users is a single column surrogate key). So you store both user's keys in the second table in their own columns (one to note who started the correspondence, the second to note who the correspondence was sent to).
Is there a way to define (in my mind, I see it as two) relationships that would relate the primary key column to both columns in the second table so you can cascade a delete? Enterprise Manager for SQL2k won't let you do this (at least that I can find).
Is there the type of scenario that a Trigger would come into play?Do you mean like:
USE Northwind
GO
SET NOCOUNT ON
CREATE TABLE myUsers99(UserId int PRIMARY KEY, UserType varchar(10))
CREATE TABLE myNotes99(UserId1 int, UserId2 int, Notes varchar(255)
, FOREIGN KEY (UserID1) REFERENCES myUsers99(UserId)
, FOREIGN KEY (UserID2) REFERENCES myUsers99(UserId)
)
GO
SET NOCOUNT OFF
DROP TABLE myNotes99
DROP TABLE myUSers99
GO|||It's called a Many-To-Many relationship.
The values in each dataset can be related to more than one value in the other dataset through an intermediary table. In your case, both datasets are the same table.|||Exactly, except I want to cascade deletes to the child table. If a user is deleted from myUsers99 then any rows in myNotes99 that reference that user ID will cause errors in the code. If you select the Cascade Delete checkbox, then Enterprise Manager throws an error when saving the changes that it creating the contraints "may cause cycles or multiple cascade path", which is actually what I want.
I could do this pretty easily through code, but I have become a fan as of late of moving as much RI maintenance into the database layer instead of runtime.|||Very dangerous. You better be damn sure of your record relationships.
You are best off doing this using a stored procedure to delete your records, but if you insist on having it tied to the schema then you could do it in a trigger.|||Blindman and Brett, thanks for the quick answers. It looks like I will rethink this idea and go about it a little differently.|||Very dangerous.
Ummm...maybe...
I never use cascading updates or deletes, and I do prefer to do all the work in a stored procedure.
As a matter of fact, I prefer to totaly isolate developers to only have EXEC authority to sprocs, so I know that no errant DML will cause any data integrity issues.
Also, the fact of having as much constraints isloated to the database the better...
So without furth ado...my first INSTEAD OF TRIGGER
USE Northwind
GO
SET NOCOUNT ON
CREATE TABLE myUsers99(UserId int PRIMARY KEY, UserType varchar(10))
CREATE TABLE myNotes99(UserId1 int, UserId2 int, Notes varchar(255)
, FOREIGN KEY (UserID1) REFERENCES myUsers99(UserId)
, FOREIGN KEY (UserID2) REFERENCES myUsers99(UserId)
)
GO
CREATE TRIGGER myTrigger99 ON myUsers99 INSTEAD OF DELETE
AS
BEGIN
DELETE FROM myNotes99
WHERE UserID1 IN (SELECT UserId FROM deleted)
OR UserID2 IN (SELECT UserId FROM deleted)
DELETE FROM myUsers99
WHERE UserId IN (SELECT UserId FROM deleted)
END
GO
INSERT INTO myUsers99(UserId, UserType)
SELECT 1,'Manager' UNION ALL
SELECT 2,'Client' UNION ALL
SELECT 3,'Scrub'
GO
INSERT INTO myNotes99(UserId1, UserId2, Notes)
SELECT 1,3,'Get to work' UNION ALL
SELECT 1,2,'Have a nice day' UNION ALL
SELECT 1,1,'Note to self...fire scrub'
GO
SELECT * FROM myNotes99
GO
DELETE FROM myUsers99 WHERE UserID = 1
GO
SELECT * FROM myUsers99
GO
SELECT * FROM myNotes99
GO
SET NOCOUNT OFF
DROP TRIGGER myTrigger99
DROP TABLE myNotes99
DROP TABLE myUSers99
GO|||NEVER use cascading updates and deletes?
Would you NEVER use a self-cleaning oven?
NEVER use cruise control?
NEVER use your home's thermostat?
Implementing cascading updates and deletes doesn't preclude you from limiting user access only to stored procs. It just means you don't have to duplicate built-in functionality with custom code!|||NEVER use cascading updates and deletes?
No. Cascading Updates. Wouldn't you consider that a new Entity? Wouldn't you want to keep history? Oh, wait, you're in the surrogate camp? Yes
Would you NEVER use a self-cleaning oven?
Never...the wife does though
NEVER use cruise control?
Actually, no, I don't...I live in the New York Metro Area...not a chance
NEVER use your home's thermostat?
I assume you mean a programmable one...I haven't figured out to program yet...(I defer to the missus...what say would I have over what the temp was)
Implementing cascading updates and deletes doesn't preclude you from limiting user access only to stored procs. It just means you don't have to duplicate built-in functionality with custom code!
For Key information only...And wasn't your earlier point that it was dangerous?
And whether it was with a trigger, constraint or a sproc, what's the difference?
OK, Here's one. Any inadvertant DML and with cascading, you could blow away an entire relational tree...ooops...
I guess you could always have a recovery procedure if you kept history|||And whether it was with a trigger, constraint or a sproc, what's the difference?the difference is how much time you have to spend writing it
:)|||Ha ha! I knew that post would provoke a heated response! ;)
As far as your comment about inadvertently blowing away a whole relational tree, if you duplicate cascading deletes in your stored proc then you may have shifted that danger, but you haven't eliminated it.|||Relying on CASCADE ON <whatever> is like believing that when you tell your wife to fill up the car, you can hope that she would fill up the windshield wipers flued which light has been on for the past 2 weeks. But guess what, - SHE DOESN'T!!! I guess the pont is that this method is for lazy DBA's, but I guess I am even lazier, which means I don't want to remember 6 months down the road that "there was a CASCADE ON DELETE last time I checked, I swear..." WRITE ONCE, REVISIT...NEVER!!!|||Comment deleted upon consideration of better judgement...|||Comment deleted upon consideration of better judgement...Hey, I liked the original post, before editing :D
Comparing it to a wife is a really unfair!|||Wow...lots to chew on!! Thanks for everyone's input! In this particular case, I am not really concerned with maintaining a history because this data is not very important. So if it gets lost its not a big deal and broken relationships will case problems because the hierarchy being consistent is pretty important for this (lesser of two evils).
In frankness, the user table will almost never have a row (user) deleted. Which is all the more reason to try and plan this in the DB rather than through a SP so programmers don't have to remember that they have to clean up such-and-such table if they delete a row from so-and-so table.
I guess if I used the deleted table method as Brett suggested I could maintain RI (just have to check both tables for messages instead of one) and set some type of scheduled batch to clean up that table from time to time ...just seems like a waste of space to lug around data that shouldn't ever be used in daily operation of the code.
I agree with blindman. Why re-invent the wheel and maintain RI through SP, Triggers, or code when the database can handle it for you automagically? Some may find it lazy (and I agree that it is if you over-rely on it), but its great most of the time.
I know MS probably has a good reason for not allowing this, but it just seems like it would a nice feature (granted, it would probably rarely be used) to be able to link one column in a table to two in another and be able to cascade deletes or updates.|||Well you have a special case, and that's one of the ways to do it.
But Cascading for me has a bigger philisopgical issue. Updates especially. You are not just messing with data.
You're messing with the key.
So if the key of a state table for New Jersey is NJ, and you want to change that key with a cascade update to all of it's children to XX, sure it's easy, but does it make sense?
Wouldn't NJ "live" historically? Wouldn't XX be a brand new entity?
While this thought doesn't apply to Deletes, Deletes are just dangerous. You want to delete a parent and all of it's children, fine, take care of it in a sproc.
MOO|||Wouldn't NJ "live" historically? only in your mind
BARK|||I don't think it would exist.
Example (admittedly very hypothetical, but go with it): If 'NJ' is being updated in your database to be something like 'N.J.' (another example is what happens if New Jersey changes its state name to 'Jersey' and the abbreviation becomes something like 'JY'), then wouldn't you want your child tables to cascade the change? If your code uses the foriegn key in the child table to look up the parent row to get the full name of the state, and you didn't cascade the update, then your code is now broken (or has to be re-written to handle the new state abbreviation...neither is an acceptable option in my opinion).
If the keys are important enough to define a relationship for, I personally think they should be important enough to cascade deletes and updates (assuming of course that the two keys are actually used *in practice* to reference parents/children and have the possibility of changing or being deleted...I know there will be exceptions to that thought). Otherwise, why use a relationship?
In my experience, it saves many-o-bottle of aspirin if the database maintains itself rather than the developers.
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.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
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
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
>
>
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
>
>
Subscribe to:
Posts (Atom)