A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

"Circular" Relationship



 
 
Thread Tools Display Modes
  #1  
Old March 19th, 2009, 03:58 PM posted to microsoft.public.access.tablesdbdesign
PeteyPueblo
external usenet poster
 
Posts: 1
Default "Circular" Relationship

I am currently restructuring the database at my company to allow for more
efficient use. I have the restructuring almost completely done, but I am
stuck on one design issue. Here are the basics of what I have:

We have fabricated parts, which are linked in a many to many relationship in
a junction table to the weld assemblies that they are a part of. This is
because many fabricated parts can be used in many different weld assemblies.

It was recently brought to my attention that there are weld assemblies that
are used in other weld assemblies. I tried to create a junction table with
two primary keys that relate back to the same weld assembly table, however, I
cannot enforce referential integrity with this relationship.

Has anyone solved a problem similar to this before? I have seen several
examples online of employees who manage other employees, but I am not sure
how to implement this kind of relationship into an Access environment.
  #2  
Old March 19th, 2009, 04:50 PM posted to microsoft.public.access.tablesdbdesign
Fred
external usenet poster
 
Posts: 1,451
Default "Circular" Relationship


We do that.

The linked fields in junction tables should be FK's, not primary keys.

This is one case where it is particularly useful to recognize that a
junction table is more than an administrative tool to do a M to M
relationship. Each record in it is a record of a relationship between two
records(in your case, between two parts) . For BOM's, it's typically a
record of the fact that that "A" is used to make "B" You'll put all of
your parts into one parts table (presumably PK = Part Number) , and link
"field A" and "field B" in your junction table to the part number field in
your parts table. .

This isn't your classic "circular" relationship where a table is linked to
itself, but I guess you could this two-table loop circular.

  #3  
Old March 19th, 2009, 05:28 PM posted to microsoft.public.access.tablesdbdesign
KARL DEWEY
external usenet poster
 
Posts: 10,767
Default "Circular" Relationship

I think you need what is known as a 'Self-join'. An example would be an
employee list that included a Foreign Key field for Supervisor. Supervisors
would themselves be employees and be in a one-to-many relationship with
employees.
In the Relationship window add the table twice. Access adds a sufix of '_1'
to the table name of the second object.
Click on the Primay Key field of first table and drag to the Foreign Key
field of the second table. Select Referential Integerity and Cascade Updates.

"PeteyPueblo" wrote:

I am currently restructuring the database at my company to allow for more
efficient use. I have the restructuring almost completely done, but I am
stuck on one design issue. Here are the basics of what I have:

We have fabricated parts, which are linked in a many to many relationship in
a junction table to the weld assemblies that they are a part of. This is
because many fabricated parts can be used in many different weld assemblies.

It was recently brought to my attention that there are weld assemblies that
are used in other weld assemblies. I tried to create a junction table with
two primary keys that relate back to the same weld assembly table, however, I
cannot enforce referential integrity with this relationship.

Has anyone solved a problem similar to this before? I have seen several
examples online of employees who manage other employees, but I am not sure
how to implement this kind of relationship into an Access environment.

  #4  
Old March 19th, 2009, 05:48 PM posted to microsoft.public.access.tablesdbdesign
Fred
external usenet poster
 
Posts: 1,451
Default "Circular" Relationship

I think that this is a many-to-many relationship. One part can be used to
make many different parts, and one part can be made up of several different
parts.

So, in my opinion, Karl's good & detailed advice applies, but an with the
addition of an intervening junction table.
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 11:00 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.