help explaining one to many relationship in access 2

[y2k1981]

Hi All,

please, can somebody help me out with something. I understand how the whol 1:M relationship works, but I was hoping somebody could explaing something in access to me. If you have a a look at the following image:

I didn't create this, it's from the sample contacts database in access, both of these relationships are 1:M relationships, but how come the one on the right has the 1 and the infinity symbol, but the one on the left doesn't?

Thanks in advance for any help

 

[RoyVidar]

If you right click on the relationships, you'll find that on the one to the right, the "Enforce Referential Integrity" check box is checked, on the left one, it's not.

"Enforce Referential Integrity" is a "quality check". In this system, you might register a contact without having a contact type, which is not allowed when you "Enforce Referential Integrity" - this will force you to have a value in the "ContactType" table before entering a contact in the "Contacts" table - you also have to register a value for contact type in the contacts table.

This might cause some problems, thus the "Arrow" pointing to the contact type table.

You see, in ordinary joins all relational databases selects fields from both tables (ore more tables, of course) based on equal values in then referencing fields. In a normal join operation, a contact without a contact type, WILL NOT BE SHOWN, therefore, in addition to not checking the "Enforce Referential Integrity" check box, the developer has entered the "Join Type" box thru the mentioned dialog box, and chosen an other join type. This would probably state something like "Include ALL records from Contacts and only those records from Contact Type where the joined fields are equal".

This is important! If you choose to not check the "Enforce Referential Integrity" check box - be sure to edit the join type, else you'll experience fewer records than you'd expect.

"Enforce Referential Integrity" - in my opinion, it's rather a bit more than a simple quality check, though, it's more like what makes a relational database tick

I'd recommend using "Enforce Referential Integrity" as default relationships. If you don't, be very careful with the join type;-)

HTH Roy-Vidar

 

[KenReay]

Hi

Is the diagram from the relationships window, or from a query design view?

looks to me like it is from query design view and relationship you are questioning is a right join ?

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[LittleSmudge]

Okay y2k1981 - I'll explain this to you - IF you tell me how you imported the image into the post. !

The two different links you have there in the image are Access's way of displaying different things

First I'll expain the one with the Arrow
This means
Show ALL records from the table Contacts and only those records from ContactType where they match.

This is called a LEFT JOIN ( or RIGHT JOIN depending on the order of the tables in the SQL string )

So if the ContactTypeId field is blank you'll still see the fields from Contacts and the fields from ContactType will appear as Null.

ALSO If the data in ContactTypeId is a value that does NOT appear in ContactType.ContactTypeId then you'll ALSO see all of the fields from Contacts and the fields from ContactType will appear as Null.


NOW, in this latter example it is clear that we have a data integrity problem. There should be no way of having data in Contacts.ContactTypeId that does not relate to a valid entry in ContactType.ContactTypeId

Access will in fact allow you to specify the rule that a foreign key can only contain an entry that matches an entry in the Primary Key field of the link
This is called 'Establishing Referential Integrity'
When Referental Integrity is established ( This is done in the Database's Relationships window ) then the link is shows with a '1' at the Primary Key end and an infinity sign at the other end.


These two types of Join can be combined.


'ope-that-'elps.



G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[y2k1981]

thank you all for your replies. Firstly LittleSmude, to insert the image, I just used some TGML as follows:

[ignore]

[/ignore].

To answer your question Ken, this is from the relationships window. You've all helped make things alot clearer. One final question, what is the difference between the relationships window and the relationships that you can create in queries? (sorry, haven't explained that very well).

Thanks again

 

[willir]

Guys

I think first we have to give a big star for sharing how to import a graphic in a posting! Anybody else want to also give him a pat on the back??

Next, Roy gets a star for referential integrity. A sadly missed step in many databases. Here is to all the orphans we have known.

Ken and LittleSmudge also have made a real good point about the contact type.

The brit's rule in this thread!!!

To answer your question regarding a relationship defined in the relationship window vs the query window...

A relationship defined in the relationship window becomes part of the schema or database design. As Roy has implied this is the best place to ensure data integrity. It also saves time and preserves consistancy when creating forms and queries later on.

But this is one gotcha. A relationship can be defined in the schema, say with DAO or ADO coding that will not appear in the relationship design window! If you delete a table, but not the join, the relationship will still be present. This is a minor beef of mine with Access. But for those who work with a large number of tables, the relationship view can be extremely overwhelming.

A relationship defined at the query level is specific to the query, and defines the query - LEFT, RIGHT, EQUI Join type of thing. If the defined query is used, the relationship and data integrity should be maintained.

Two gotchas. First, the obvious. If you create a form or report without using the query, then the relationship will have to be recreated. Now what happens if you don't create the join the same way???

Second, if you use the defined query for a combo box or something, and you change the query, you just changed the query every where else it is used. Is this what you wanted? For example, for a form you want to see all records on a LEFT join, but for the report you only want to see the EQUI joined info.

Back to using the relationship design view to define relations. Some details...
- After doing this step properly, everytime you create a query, form or report using the impacted tables, the relationship will be defined for you.
- By enformcing referential integrity, you will prevent data integrity errors -- avoid lost orphans, etc.

I can't wait to try importing a graphic.

Richard

 

[LittleSmudge]

Second, if you use the defined query for a combo box or something, and you change the query, you just changed the query every where else it is used. Is this what you wanted?

Which is exacly why I rarely ever have any 'defined queries' in my databases at all.

I write the SQL string that I need dirtectly into the RowSource on combo boxes and List boxes, RecordSource on Forms, Reports etc. Then you are absolutly sure that a local modification does not propogate across the entire db.

Another advantage of placing the SQL directly in the object's property is that you can change the SQL ( modify the Where clause etc ) at run time using code.



Graham.

 

[jhowley58]

This vaguely to do with this thread. ))

I read the advice given on here and went back to enforce referential integrity. Unfortunately I've got 80+ tables so it will be quite a job.

Reading the documentation it says that if you delete a primary (one) table and deletions are cascaded then it will delete all the related (many) tables. Why cannot it just nullify the related foreign keys in the related tables?

JohnH

 

[willir]

jhowley58

Are you talking about deleting tables or records? Cascading deletions are usually done at the record level.

If you don't want to delete the child records, then don't use the cascade deletes.

But this goes back to relational integrity and relevance. If you have child records without a parent (orphan), do the child records provide meaningful information? And how will one find them? If you delete the invoice header record, the invoice detail records will provide product code, quantity and price but will not have the customer name, date, shipping info, payment info, etc.

My take is that I force the user to delete children recrds first before allowing for the deletion of the parent record.

Richard

 

[jhowley58]

Sorry Richard , I meant records.

This is a typical case: My client here is a company who market Auto BodyShop repair equipment through national distributors who, in turn, supply to end users (car body repairers).

One of my clients activities is the 'Demonstration'. This involves an end user, but may [optionally] also involve a distributor. An end user or a distributor will be related to many demonstrations. A distributor therefore has a one-many [optional] relationship with Demonstrations. If a distributor is deleted from the system it does not follow that any associated demontrations should also be deleted. However, we would want to nullify the Distributor_Demonstration relationship. A Demonstration record can validly exist without an associated Distributor.

As for finding the records, I provide entries into the database [via Forms] according to the users' needs. For example, a person setting up a demonstration activity may enter the database via a Demonstrations overview form. I can see the argument for deleting junction records totally. But the demonstration record is not a child of the distributor. It is an entity that can exist without any parents per se.

The above may not be the best example but I hope you get the general idea.

JohnH

 

[lespaul]

Instead of deleting the record another option would be to just flag it? Insert a new field in your table 'STATUS' and set this to either 'active' or 'inactive', when you run your queries only find those that are 'active' that way you don't delete the parent record, but it also doesn't show up when you don't need it.

Leslie

 

[willir]

John

Basically, you have a car or cars that may belong to this vendor or that. And you want to delete the vender but keep the car.

I like Leslie's input -- this goes a long way to show what inventory is active, or what ever.

I would also create a vender, say JohnH.

You assign a car to a vender. When it is returned, assign it back to JohnH. By querying vender JohnH, you will know what can be assigned out. This way, you maintain your relationships. Just the parent changes.

Was I close on this or still out to lunch?

Richard

 

[jhowley58]

Thanks Richard and Leslie..

I do use an 'Extinct' boolean in cases where my client no longer does business with certain clients. My client agrees with me that primary records (as opposed to junction or 'reference' records) should never be deleted.

My original question was really to do with the internal workings of MS Access I guess. What I will do is enforce referential integrity but use no cascading. I can knock up some VB to to reset any defunct foreign keys to NULL.

Thanks again,

JohnH

 

[LittleSmudge]

Basically, you have a car or cars that may belong to this vendor or that. And you want to delete the vender but keep the car.

I see what you are trying to do - but you don't want to do it as you phrased it. It you do you end up with Cars that are not owned by anyone.
Evidently this is not a valid situation - so you don't want your database to be able to say it does exist. What you do is establish referential integrity to ensure that a link is always valid - then when the car 'comes back' you assign it to you default stock vendor.

Only when a particular vendor has returned all of their cars will the database then allow you to delete that vendor.



G.