UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
2 Pages V < 1 2  (Go to first unread post)
   Reply to this topicStart new topic
> Renaming A Self Join Table In Relationships, Access 2016    
 
   
isladogs
post Dec 2 2018, 07:02 AM
Post#21



Posts: 774
Joined: 4-June 18
From: Somerset, UK


Apologies – this is quite a lengthy answer

Going back to post 16, I’d never done a CREATE TABLE statement which also referenced an existing table
Thank you for reminding me of the syntax to do that.
I’ve now replicated your CREATE TABLE statement (after making tbl2) and agree it creates the same relationship diagram automatically that you showed in that post.
I agree the diagram is flawed but not for the reason you described – I’ll return to that later

In post 17, I created the 2 tables separately and added the joins manually using one of each table
As already stated with that relationship it is impossible to create RI between the tables

Now if instead I add a second copy of Table2, I can add RI but there is one important difference in the diagram compared to yours from post 17. Both methods shown below with the difference higlighted

Attached File  Capture.PNG ( 10.87K )Number of downloads: 1


By adding the tables & relationships separately you correctly create a 1-1 join between X and A.

With your approach you get a completely incorrect 1 to many join which cannot actually occur for two PK fields
Indeed, if you delete that link and then manually relink those tables, Access creates a 1-1 join.

In both cases, a record cannot be added to Table1 (or tbl1) unless that record already exists in Table2 (tbl2)
A query containing all three fields will of course only show output where all 3 fields are equal

As I originally stated, I view this setup as very peculiar and cannot envisage any situation where I would ever create links like that in the relationships window though I will do so as appropriate if the query designer or a SQL statement

I don’t see how your examples in post 20 work either. The tables wouldn’t be normalised as described
Lets just look at your first example for customer orders.
You might have 3 tables for this part:
a) Orders – OrderID(PK), CustomerID etc
b) Customer – CustomerID(PK), LastName etc
c) Addresses – AddressID(PK), CustomerID, Address1, Address2, Town, Postcode, AddressType

The Addresses table may of course just use a single field for an AddressBlock etc
The AddressType field would have values like Billing, Delivery etc

I would never have an Addresses table with both BillingAddress & DeliveryAddress fields
So my customer order could have 2 separate records in the Addresses field even though both might be the same
Or it could just have the second record for DeliveryAddress where this is different to the BillingAddress (or vice versa)

So of course a query could have one table referencing a second table twice, but, unless I’m missing the point (quite possible), not as you’ve described it.

Looking forward to your reply ....

P.S. Apologies to Jon - this has wandered a long way from the original question.
This post has been edited by isladogs: Dec 2 2018, 07:04 AM
Go to the top of the page
 
nvogel
post Dec 2 2018, 07:51 AM
Post#22



Posts: 891
Joined: 26-January 14
From: London, UK


You are right that in the example I posed the Relationships diagram misleadingly shows the 1-1 relationship as 1-many. It is a purely cosmetic bug however, because the constraints behave exactly as they should.

The examples I had in mind in post #20 were like the following. No one-to-one relationships here, my point was that it's reasonable to have a table with more than one foreign key referencing a second table.

CREATE TABLE Order ( ... , (BillingAddressId) REFERENCES Address(AddressId), (DeliveryAddressId) REFERENCES Address(AddressId), PRIMARY KEY (OrderNum));
CREATE TABLE Trade ( ... , (NumCurrencyCode) REFERENCES Currency(CurrencyCode), (DenomCurrencyCode) REFERENCES Currency(CurrencyCode), PRIMARY KEY (TradeId));
CREATE TABLE Org ( ... , (EmployeeNum) REFERENCES Employee(EmployeeNum), (ManagerEmployeeNum) REFERENCES Employee(EmployeeNum), PRIMARY KEY (OrgEntity, EmployeeNum));
Go to the top of the page
 
isladogs
post Dec 2 2018, 08:53 AM
Post#23



Posts: 774
Joined: 4-June 18
From: Somerset, UK


We are in total agreement that one table can have more than one foreign key each referencing different fields in a second table
BUT that's different from what you originally described

I tried creating the first example you gave
a) Created table Address with fields AddressID (PK) and DeliveryAddressID
b) Modified your create table string to
CODE
CurrentDb.Execute "CREATE TABLE Order (OrderNum, BillingAddressId) REFERENCES Address(DeliveryAddressId) REFERENCES Address(AddressId), PRIMARY KEY OrderNum);"

That failed - syntax error

CODE
CurrentDb.Execute "CREATE TABLE Order (OrderNum INT NOT NULL PRIMARY KEY REFERENCES Address (DeliveryAddressId), BillingAddressId INT NOT NULL REFERENCES Address(AddressId));"

To my surprise that also failed with a syntax error

So I created the Order table manually with fields OrderNum (PK) & BillingAddressID and then created the relationship which I believe you meant

Attached File  RelationshipsOrders.PNG ( 6.6K )Number of downloads: 0


Is that what you meant?
This post has been edited by isladogs: Dec 2 2018, 08:57 AM
Go to the top of the page
 
nvogel
post Dec 2 2018, 09:57 AM
Post#24



Posts: 891
Joined: 26-January 14
From: London, UK


The following is what I meant. I tested this code and it works if you exectute the two CREATE TABLE statements separately.

CREATE TABLE Address (AddressId INT NOT NULL PRIMARY KEY);

CREATE TABLE Order (OrderNum INT NOT NULL PRIMARY KEY, BillingAddressId INT NOT NULL REFERENCES Address(AddressId), DeliveryAddressId INT NOT NULL REFERENCES Address (AddressId));

Apologies if I wasn't clear and you thought I was talking about something else. My only point was that the Relationships window does not do a good job of showing two foreign keys in a table, A, if both foreign keys reference the same table B. The fact that I happened to make one of the foreign keys also a primary key in a previous example was irrelevant to the point I was making, although it did show up yet another defect with the Relationships window which you pointed out previously.
Go to the top of the page
 
isladogs
post Dec 2 2018, 11:32 AM
Post#25



Posts: 774
Joined: 4-June 18
From: Somerset, UK


Hi again

Yes - that does indeed work and, for the benefit of anyone else following this thread, creates this relationship automatically

Attached File  nvogelRel.PNG ( 7K )Number of downloads: 0


However, I remain totally unconvinced that there is any value ‘defining’ the relationships in this way at table level

In the attached database I added an Address field & created some example addresses and example orders
I then created Query1 & added both tables. For information, the query designer automatically showed the links like this!

Attached File  nvogelQuery1.PNG ( 17.24K )Number of downloads: 0


Of course, using this will only show Orders where BillingAddressID & DeliveryAddressID are identical.

If you want to see all results, you need to REMOVE one link and add a 2nd copy of the Address table then relink.
This is Query2. Note that RI isn’t shown for the link added manually

Attached File  nvogelQuery2.PNG ( 21.49K )Number of downloads: 1


As I stated previously, there are numerous examples where 2 foreign keys might be linked to the same field in a second table using a query.
In case, I’ve also not been clear, the point I have been making all along is that I see no benefit in doing so at the application level via the relationships window.
In fact, as the above example may have illustrated, I would state that doing so is counterproductive.

Do we agree about that?

This is of course a long way from the topic title ‘Renaming a Self Join Table in Relationships’.
Once again, apologies to Jon.

Go to the top of the page
 
nvogel
post Dec 2 2018, 12:15 PM
Post#26



Posts: 891
Joined: 26-January 14
From: London, UK


The point of the foreign keys is to impose data integrity constraints on data and the only way to do that is to define them "at table level" (as you put it). Data integrity is not counterproductive and I don't know why you think it would be! I certainly don't agree with you on that.

As I pointed out in my first reply in this thread, joins and foreign keys are completely different things and it's extremely unfortunate that some (not all) Access users seem to get them confused. The confusion doesn't exist among users of other database software. I have nothing much to say about joins and queries since that entirely depends on what results you want from your query and anyway I never use the query designer to build queries because I can write SQL very easily without it. The fact that you can write different queries on top of tables doesn't change the nature of the underlying referential relationship (foreign keys) in the tables.
This post has been edited by nvogel: Dec 2 2018, 12:26 PM
Go to the top of the page
 
isladogs
post Dec 2 2018, 12:49 PM
Post#27



Posts: 774
Joined: 4-June 18
From: Somerset, UK


QUOTE
The point of the foreign keys is to impose data integrity constraints on data and the only way to do that is to define them "at table level" (as you put it). Data integrity is not counterproductive and I don't know why you think it would be! I certainly don't agree with you on that.


I wasn't intending to imply the referential integrity shouldn't be used.
If that's how it came across, then I explained myself very badly
Yes of course I apply RI and use cascade update/delete in many cases as well
What I did mean was that its highly unlikely I would create a 'double join' in the relationships window and my example queries were intended to illustrate why.

I'm also happy using SQL instead of the query designer but pictures are useful for understanding in my opinion.
I certainly didn't state that altering the links between tables in a query or SQL statement in any way alters the underlying RI between the tables

Last time, I meant to include the simple database I used to create my screenshots.
I've attached it this time in case it is of use to anyone.
This post has been edited by isladogs: Dec 2 2018, 12:52 PM
Attached File(s)
Attached File  nvogel.zip ( 23.65K )Number of downloads: 5
 
Go to the top of the page
 
nvogel
post Dec 2 2018, 02:14 PM
Post#28



Posts: 891
Joined: 26-January 14
From: London, UK


Thanks for the examples. Like I said before, there is no actual benefit in "relationships" in Access that I'm aware of. Foreign keys are important for referential integrity but relationships are not important.

I don't even want to think about the strange stuff that Access does with "relationships" (which are really joins) when defining queries. Access gets the whole idea just wrong in my opinion.
Go to the top of the page
 
isladogs
post Dec 2 2018, 03:19 PM
Post#29



Posts: 774
Joined: 4-June 18
From: Somerset, UK


Thanks.
Its been an interesting exchange but I think we've probably now thrashed it to death.
Have a good evening.
Hope the weather in London is better than it is here
Go to the top of the page
 
JonSmith
post Dec 3 2018, 03:38 AM
Post#30



Posts: 3,980
Joined: 19-October 10



QUOTE
This is of course a long way from the topic title ‘Renaming a Self Join Table in Relationships’.
Once again, apologies to Jon.


Go ahead guys. Mine is a rather superficial issue, I don't mind a tangent to an interesting discussion.
I am mostly using the relationships window here since I am trying to design a database model with a 'data engineer' here. The visual representation is the best way to communicate the information to him.
Go to the top of the page
 
nvogel
post Dec 3 2018, 07:48 AM
Post#31



Posts: 891
Joined: 26-January 14
From: London, UK


The diagrams produced by Access don't use any of the standard notations used for data modelling and they leave out most of the useful information that users of data models want to know about: alternate keys, foreign keys, optionality, multiplicity, etc.

Perhaps your data engineer would find an actual ER diagram more useful than what Access produces. If you don't have a suitable modelling tool already then there are plenty of good options you can download, purchase cheaply or use online. See: http://www.databaseanswers.org/modelling_tools.htm


Go to the top of the page
 
JonSmith
post Dec 3 2018, 10:34 AM
Post#32



Posts: 3,980
Joined: 19-October 10



Yeh, I don't think that would help him to be honest. I think the limits of Access diagrams is the least of the issues. I think a book on relational database design would be the most useful but I don't think I can suggest that.

Btw, what do you mean it cannot show foreign keys, those are easy to see in the Access relationship window?
Go to the top of the page
 
isladogs
post Dec 3 2018, 11:36 AM
Post#33



Posts: 774
Joined: 4-June 18
From: Somerset, UK


Nigel
I thought someone had already mentioned Allen Browne's version of the Relationships report with field info but it must have been a different thread
See what you think: http://allenbrowne.com/AppRelReportCode.html
Go to the top of the page
 
nvogel
post Dec 3 2018, 05:24 PM
Post#34



Posts: 891
Joined: 26-January 14
From: London, UK


QUOTE
Btw, what do you mean it cannot show foreign keys

In diagrams using IE, IDEF1X or UML notation the foreign key attributes are usually labelled as "FK". This becomes important if you have sub-diagrams where not all the related tables are shown in one diagram and so you can't rely on relationship lines to identify foreign keys. In Access you can't tell which attributes are foreign keys unless you add the referenced table to the diagram.
Go to the top of the page
 
2 Pages V < 1 2


Custom Search


RSSSearch   Top   Lo-Fi    12th December 2018 - 12:04 AM